Combining Excel Multiple Spreadsheets into One AND show in a separate column the source

Hi all,

I need to combine multiple Excel Tabs (with different schema) from one Excel File into one tab AND I need to know where the data came from, so which row came from which tab.

Reason is, some of the tabs contains a year and I need the year to make further calculations.

Example: Sheet one "2020 Control Statement", Sheet two "2019 Control Statement", etc. and although the data are similar, there are sometimes a different number of columns (therefore different scheme).

In my imagination I should do something like the Union and put the data from sheet "2019 Control Statement" under the data from sheet "2020 Control Statement" and put also an additional column "Sheet Name" with the respective tab names.

Thank you,

Katha

Tips and Tricks

Batch Macro

Help

Preparation

Accepted answers

DannyS

Hi @Katha ,

I would highly recommend checking out this article: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/Read-in-Multiple-Excel-Files-with-Multiple-Tabs-that-have/ta-p/51145 to get to your desired outcome. There are guided instructions and a sample workflow to help you out!

And then one thing to make sure to do to get the file names is to select the dropdown in option 5 of your Input data tool which allows you to "Output File Name as Field".

Hope this helps!

Best,

Danny

All comments

DannyS

Hi @Katha ,

And then one thing to make sure to do to get the file names is to select the dropdown in option 5 of your Input data tool which allows you to "Output File Name as Field".

Hope this helps!

Best,

Danny

Katha

Hi @DannyS,

Thanks a lot for looking into it.

One issue is fixed. I could manage to bring all my sheets together into one. 🙂

But I still facing the problem that I don't know where the data (lines) come from, so from which tab.

Do you have any further ideas or proposals how I can fix this?

Thanks,

Katha

Hi DennyS,

I just tried it again but this time I did the change you suggested below "Output File Name as Field2 in the Macro it self and that works exactly like I wanted.

Thanks a lot! 😊

Best,

Katha

Quick Links

This months top contributors

SophiaF 432

AkimasaKajitani 390

mceleavey 388

mbarone 337

michael_weaver 335