how to pick columns based on a sample file and picking it from different excel files ?

[Binu456m]

i have multiple excel files which has many sheets inside it. So i need to get an output of certain fields based on the sample file attached. The excel formats are same for all input files (the input files can be two files or hundreds of files). i have attached the input file format and sample Output data i need to extract from the files i upload. if anyone of you can help me, would be appreciated 

[caltang]

@Binu456m PFA.

I don't know what constitutes Budget Summary:

[Binu456m]

Budget summary is just the Sheet name for reference.. it doesnt have any value

[caltang · Accepted solution]

Then you can get it from the file name if you can change it to full path from the input, you should get the sheet name also. 

If the above solved your need, kindly like & mark as accepted solution so that you may help others find the solution more quickly + to close the thread as is. Thanks!

Best regards,
Calvin Tang
https://www.linkedin.com/in/calvintangkw/

[Emmanuel_G]

@Binu456m  Hi !

Can you please confirm me that this first version is ok for you ?

Do not hesitate if there is any question please.

Cheers !

[Binu456m]

excellent.. i got the output.. but Total estimated hours and Billable hours per month (total) is null

[Emmanuel_G]

@Binu456m  Great !

Find in attachement the corrected version for billable and total estimated hours.

Do not hesitate to mark the answer as solution to help others.

[Binu456m]

You are a life saver.. Excellent work.. But how did you do that? can i have some insights?

[Emmanuel_G · Accepted solution]

@Binu456m 

As we discussed, the main steps are :

1 - Get a macro to read and concatenate all the files in directory

2 - then filter on the labels you want in the output template. AT this step, I have selected the fields that contains labels and the corresponding values (BDO, F3, F5, F17)

3 - finally change the layout by using either crosstabl tool. It allows us to have one line for each filename as you said there are more than 100 files.

Do not hesitate to mark answer as solution to help others.

[Binu456m]

so in future, if i need additional columns from other tabs, i shd approch the same method right? 

Thanks A TON :) i really mean it