Combining Multiple Excel Files with Different Tab Name in Each File

Question

Hi!

I am looking for a way to combine multiple excel files in which the tab names are based on the excel file name (so the tab names are different between each file) and the column names are also different and in different spots, but have the same type of data (ex. sales dollar vs. sales dollars). Is there a way to do this?

mbabinski · Answer

Unfortunately, the file schema for the files i am uploading are different, so I am able to pull in the files with different sheet names but not organize the data is a similar way since the schema is different.

Qiu · Answer

@mbabinski 
There is a very mature and good tool posted.

Multi Sheet Import Example.yxzp

AncientPandaman · Answer

it can be solved by create a macro.

first, load your excel by list of sheet names.

then, create a macro like this.

macro with configuration

you can do filter on before macro to identify which sheet you want to take.

Finally, here the final outcome, you can do the data processing afterwards.