Duplicating one workflow for multiple sheets of the same format (Help!)

[mmishal001]

I've created a workflow which cleans data on one sheet and organises it. Essentially, its summarises 240 lines from one sheet into 12 lines of output.

I've 80 sheets like on one excel file. They are all of the same format.

How do I duplicate this workflow for all the sheets without copy pasting this workflow 80 times. The output - with 80 sheets, I'm looking to get a summary table of 960 lines of output.

I presume this has something to do with recording a macro but couldn't find out how it works.

Would be grateful if someone the most efficient way of doing this. I've attached the workflow and example file for your reference.

[BrandonB · Accepted solution]

This is a perfect case for turning your workflow into a macro! It basically makes a workflow a repeatable process that you could send a list of file paths into by using a control parameter that will perform the steps in the workflow for every file fed in.

Take a look at this training video for building your first macro:

https://community.alteryx.com/t5/Videos/Build-Your-First-Macro/td-p/51352

[TonyA]

I tossed together a quick solution to give you an idea of what this might look like. But this will be very hard to understand if you don't follow @BrandonB 's advice and review the video training. You also might want to check out the video on batch macros at https://community.alteryx.com/t5/Videos/Build-Your-First-Batch-Macro/m-p/52900

[TonyA]

Hi @mmishal001 .

If any of these responses have helped you resolve you problem, please mark them as solutions so others with a similar issue can find them. If you still would like more help with this please let us know.

Thanks!

Tony

[mmishal001]

Thank you @TonyA @BrandonB for your reply.

I'm able to create a Macro for one sheet and it works great! I'm looking to replicate this for each sheet (100 of them).

Is there any way this can be done without having a 100 input tools and attaching the Macro tool that I've created to each one of them.

I'm aware of the Dynamic select tool however this stacks the sheets one on top of the other and the Macro doesn't identify each of the individual sheets.

Attached working files.

[BrandonB]

Have you seen the option to just pull in the list of sheet names? You can use this option and have a wildcard replace your file name to pull the list of all files and all of their sheet names in one Input Data tool. 

 

Make sure you also choose the option in the configuration window to "Output File Name as Field" so that you can use these in conjunction in a formula following: 

 

Finally, make sure that inside of the macro that you built, you open up the interface designer (View -> Interface Designer), and change to these options on the settings window within (little gear icon). This allows for your macro to work on files that have different schemas. You can also change your macro icon here if you want to use your own custom icon.

 

 

 

[mmishal001]

Thanks @BrandonB for you response.

I'm aware of bringing multiple sheets but am unable to get the macro to run across each of the multiple sheets.

I presume my macro input has to be corrected but I am not sure how exactly it needs to be corrected such that it can read multiple sheets and apply the macro to each sheet individually and stack it one on top of the other. Any help is much appreciated!

[BrandonB]

When you click on the macro you should see the drop down for the field that you want to pull in. You will want to choose the file name field. Because we have added the sheet name to the file name in the formula tool, each sheet will be fed into the macro. Inside of the macro you can also choose the option in the input data tool to have the file name as a field so that in the stacked data you can see what sheet it is coming from.

[mmishal001]

Thanks @BrandonB I really appreciate your quick response!

My macro is set up where it does the transformation for one sheet. So when I drag the macro input tool it gives me the below list.

I then input the file which has multiple sheets on another workflow (attached sample). However it doesnt apply the workflow to each sheet separately even though I've loaded it as file name.

[TonyA · Accepted solution]

I grabbed a macro I've used before for pulling in disparate sheets from an Excel file and added your workflow to process the PT sheets. This pretty much incorporates the techniques Brandon suggests. It is a batch macro that iterates once for each sheet, using the sheet as the input for sheet name and the filename+sheet as the template. It also appends the sheet name to the output so you can see which rows come from which sheet.

The calculations in your workflow are very sensitive to the layout of the sheet so I couldn't easily remove columns to show you that the sheets can have different schema but the structure of the macro does support this. I did take out the course values in one sheet to show at least one difference,