Automated merging of separate excel sheets into a singular, consolidated sheet

[mald117]

Hello!

I am working to merge worksheets in similar formats into one consolidated set. 

For context, people are inputting data into the same style design template but each is split out into 22 - 26 excel spreadsheets. I am looking for an automated way to feed in their inputs into alteryx to create one consolidated worksheet. We can add a column for the title of the design sheet to help split out the consolidated spreadsheet. Any thoughts / ideas? 

Thanks! 

[AbhilashR · Accepted solution]

Hi @mald117, would the attached solution serve your purpose? File Directory identifies the file to be read into Alteryx, the macro after it identifies the sheet name, and the macro after that uses the information for the previous tools to bring in the data as one consolidated dataset. Let us know.

[mald117]

Thank you so much!! Much appreciated

[nirkisna65]

Hello @AbhilashR  

What if the each of spreadsheet has the color formatting/colored cells. Is there a way to combine all the sheet into one with Color formatting intact?

Also, I would only want to merge first tab of the each file. Currently all of the spreadsheet I want to combine has 3 tabs.

Thank you.