Merging Multiple Tabs into One Spreadsheet with Formatting

[kv-defy]

I am currently working on data in multiple excel tabs. 

I have successfully cleaned and formatted the dataset for 1 tab with a current workflow. 

However, I have multiple tabs which I want to combine them into 1 spreadsheet using Alteryx. However, I do not know of the fastest and efficient way of doing this because Alteryx's input data only takes in 1 tab at a time. I have more than 50+ different tabs (countries) to clean and format the data. I would like to combine them into 1 spreadsheet.

Current data format:

	2021	2020	Variance	... (more columns)	Variance %
Revenue
IT Cost
... (more rows)
Other Costs

 (sample, there are more than 50 tabs to merge together)

Desired data format:

	2021	2020	Variance	... (more columns)	Variance %
Revenue
IT Cost
... (more rows)
Other Costs

with the other countries' data tab combined into 1 spreadsheet.

Is there an optimal way of doing this? Kindly seek help and understanding 😊

[grazitti_sapna · Accepted solution]

Hi @kv-defy, you can do it with the help of macro. 

Firstly, I have taken an input just for sheet names.

Then passed it through the macro to get the result from all the tabs.

You can simply replace the input source with your source and then follow the configuration from the first screenshot.

PS: You might have to make some tweaks as per the requirement. This workflow is a generic approach.

I hope this helps!

Thanks!

[DawnDuong · Accepted solution]

hi @kv-defy 

The question you raised is arguably the most-asked question of all time. And many others have answered this before. Some useful resources are:

1. Import multiple sheets or specific excel range: https://community.alteryx.com/t5/Alteryx-Designer-Knowledge-Base/How-To-Import-Multiple-Excel-Sheets...
2. Import multiple excel sheets from different schemas https://www.linkedin.com/pulse/how-import-multiple-excel-sheets-files-different-schema-bingqian-gao/
3. For simple case like the one in question, the dynamic input tool can do it for you. Check out the Example workflow (which comes together with the standard installation anyway -- attached here for your convenience only).

[SouliereS]

Your article content is being very much interested, I am very impressed with your post. I hope to receive more great posts.

Direct2HR Schedule