combine multiple excel files

Question

hello every one,
i have a lot of files with the same format.
every file have 2 sheets. sheet for every test i made.
i want to copy part of the data (under&nbsp;Vcollector and under&nbsp;Icollector) in the first sheet to new excel file (master file) and part of the data (under Vgate and under Icollector) in the second sheet also to the master file but to different sheet.
&nbsp;
any idea please?
thanks :)

wyn hopkins · Answer

Hi Tsuriel,Does each sheet always have the same name in each workbook?What version of Excel are you using and have you ever used Power Query ?   It's great at this sort of thing.

tsuriel avraham · Answer

hello wyn,
thank for your respond :)
the names are always like List(xxx) but its arbitrary.
i am using 2016.
yes i try to play with the power query but i didnt succeed :(
&nbsp;
thank again :)

wyn hopkins · Answer

Hi&nbsp;Tsuriel Avraham,
&nbsp;
Take a look at this and see if it's on the right track&nbsp; (I've attached a file)
&nbsp;

tsuriel avraham · Answer

hello wyn,
why is the tables is append below the previous table and not beside the table?
i dond fully understand what you done, did you copy only the requested coulmn or all the table as is?
&nbsp;
Tsuriel :)

wyn hopkins · Answer

Hi TsurielI've used Power Query to pick the required columns from the files  (you mention you have multiple files, so I've built it to get the data from all the files in a particular folder)The data from each sheet is then appended on top of each other to give you a single data set.   You could then use Pivot Tables to slice and dice the dataWere you wanting something else?

Forum Discussion

combine multiple excel files

Share

Resources