Merging multiple tabs and include a column which indicates the tab name

The data is a snippet of the dataset. I have currently created a workflow that specifically takes in a tab of the excel sheet and populate the desired data. Is there a way to do this for all the other tabs using macro?

I have initially tried to combine the data for all the sheet tabs (more than 50 tabs), the data gets very messy and very hard for me to read as there are thousands of rows and hundreds of columns. I also initially wanted to do the workflow for each tab manually by changing the tab name. However, it is too burdensome and tedious because I have 50 tabs. I would like to automate this which involves lesser human intervention.

I would instead like to do the workflow 1 by 1 for each tab using macro and then merge them together with a new column that represents the tab name when the data for each tab are cleansed and modified. Is it possible for the workflow to populate each tab and then have another workflow to merge them together?

Alternatively, is there better ways of doing it? I am still new towards Alteryx and would need to seek advices from you.

Attached is the test file.

kv-defy-testfile.xlsx

Input

Dynamic Processing

Preparation

Accepted answers

SPetrie

Are you doing the same cleansing and modifications in each tab, or are you doing different things for each one?

If you are doing the same modifications on each sheet, then this is a perfect use case for a batch macro.

You first bring in a list of sheet names for the excel sheet and the full path of the file name. Replace the <List of Sheet Names> with the actual sheet names and you have the setup you need to feed the macro.

For the batch macro portion, you need a file input and have a control parameter to accept the full path to the file/sheet and an action tool to update the file/sheet being opened.

For the sheet name, I choose to be fancy and lazy at the same time. We already have the sheet name thanks to the initial list of sheet names, so I feed that to a second control parameter. I changed the name of that parameter to something easy for me to identify and then I can reference it in a formula tool using a reference shortcut.

That sounds and looks a bit daunting, but it only takes a few moments to actually setup so its worth it. Alternatively you can have the file input add the full file name and parse the tab name from that or have a second action tool to update a formula field, but I find this method to look and feel cleaner.

After that initial setup, its really just adding any formulas and transformations you wish to happen on each sheet.

In the interface designer for the macro, check the radio button to auto configure by name.

Save the macro, add it to your workflow and point it to the columns that contain the sheet name and full file path.

I attached the sample workflow with macro for you to take a look at. Hopefully this helps with your task.

Let me know if this isnt quite what you are looking for or if you need additional clarification.

example.yxzp

SPetrie

If you changed the file input in the macro to have the range, then it probably broke the action tool. It was looking for Amsterdam to be replaced by each item but the addition of a range would have changed what it was replacing.

It may be easier to add the range to the end of the file name you are passing to the macro instead of adding the range to the macro itself. That way the macro can still be used for other workflows where you may want the full range or a different range.

You can add the range into the formula tool before the macro.

Replace([FileName],"<List of Sheet Names>","`"+[Sheet Names]+"$A1:D5`")

If you are still having issues, you would need to upload a copy of your workflow for me to look at. Its hard to guess what may be going wrong without seeing the changes you have made.

All comments