combine multiple excel files

[Tsuriel Avraham]

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 Vcollector and under 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.

any idea please?

thanks :)

[#2]

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.

[#3]

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 :(

thank again :)

[#4]

Hi @Tsuriel Avraham,

Take a look at this and see if it's on the right track  (I've attached a file)

[#5]

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?

Tsuriel :)

[#6]

Hi Tsuriel

I'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 data

Were you wanting something else?

[#7]

i understand :)

how can i modify which coulmn to take?

[#8]

If you go to Data > Queries and Connections and then double click on the Transform Sample File

Then click on the Rename Column Step and name the columns you want to keep followed by the cog next to the Remove Other Column step