Loading Multiple Workbooks, with multiple Worksheets, Filtering, then Unioning...

[andrewestes]

Hello!

After about 2 hours, figured it's time to ask for some help... Problem #2 is really what the issue is if you want to skip the backstory. 

There is a monthly report with 3-16 tabs which comes from 25 different people.

22 of those people follow the same general pattern with respect to column position although there might be a slightly different naming convention. 

My Thought Process

1. Load the data 
2. Filter only the relevant month
3. Union by Position where applicable
4. Union by Name the 3 Outliers 
5. Export

Problem #1

Dealing with monthly data means tab names are inconsistently named. April 2025 vs Apr 2025

I can manually filter for the name changes - there's not that many variations...

Problem #2

I've imported each workbook with it's own Input Data tool with "List of Sheet Names" selected as the Table/Query.

When viewing the output anchor of the Input Data tool, the Sheet Names show up as expected. 

However when filtering for "April 2025" and then Browse, no data shows - only the sheet name. 

This is not the case when the Input tool has the month already selected as the Table/Query. 

I don't understand why I would need to specify which tab I want as part of the Input tool rather than loading all tabs and then filtering out the one I actually need for that month. 

Problem #3

Is there a way to also include the incoming data source as part of the Union?

Person A and Person B union their reports, and as part of the Union-ed data, you'll see which row comes from which person and that data is added as a column at the end. 

I know this can be done as part of the data prep process, just wondering if there's any other ways to do it. 

[abacon]

@andrewestes When using "List of Sheet Names" function, it is checking the files sheet names and not pulling the data for each of the sheet names. You have to do a 2 step process where you get the sheetnames, and then pass it back through another tool to pull the data out using the sheet names, usually via a macro. Try the attached workflow, it has 2 macros, one to get the sheet names and the other to get the data from them.

For your Problem #2, you really only need the formula tool that preps the filename field (formula (7)) and the 2nd macro (outputdata (8)).

If this solves your problem, please mark as the solution so others may find it fast.

abacon

[tim-regas]

Hi,

I think the step you are missing is one more dynamic input tool to incorporate the list of sheet names into the file name.

First, instead of using an input tool for each file, you can use a directory input to get a list of all Excel files in the specific folder. From there append "|||<List of Sheet Names>" to the end of the file path and feed that into a dynamic input tool.

In the first dynamic input tool edit the data source template and select Full Path for the output file name. This will output the sheet names as well as the full file name for where those sheets came from.

Using a second formula tool you can replace the list of sheet names with the actual sheet names with the following formula: Replace([FileName], '<List of Sheet Names>', [Sheet Names]).

From here you can connect to a final dynamic input tool to pull the data from all sheets in all files.

[andrewestes]

This looks like it should work but I keep getting errors on the second macro... 

"Tool #5: "March 2025" does not match a sheet or named range or the file is corrupted"

This happened for one person's file. 

The same messaged occurred for sheets labeled "11-2024" and "06-2024" in another person's file. 

I removed any trailing/leading whitespace and the data input is 100% ok. 

The files themselves are not corrupted. 

Each person follows the same naming conventions in their respective files so it's interesting that 11-2024 failed but 10-2024 was successful, and that April 2025 was successful but March 2025 failed. 

[abacon]

@andrewestes You are using the Macro I provided or the configuration provided by Tim Regas?

if it’s mine, could you provide a screenshot of the data being passed through and of the 2nd macro settings for further help troubleshooting? This is most likely going to be something small and odd that’s erroring it out.

[apathetichell · Accepted solution]

Hey --- ignore the suggestion to use dynamic input --- this is a batch macro case --- dynamic input has ONE use with multi-sheet excel files --- that is when schema is perfectly identical. That is not your case - so ignore it. Use the batch macro. you need an inner/outer system where macro 1) reads in the sheet names of the file (--- <List of Sheet Names>) --- and you can then parse using filter to find the sheets that fit your logic (ie that have dates).... You then use a formula tool to replace <List of Sheet Names> with the sheetname. That full path is fed into macro 2. You use interface designer to union by name.

having said that --- I think union by position for anything over say 5 columns can be ROUGH. I'd recommend using dynamic rename to create a standardized column name -- and then a union by name.

[caltang · Accepted solution]

This should guide you well: https://knowledge.alteryx.com/index/s/article/The-Ultimate-Input-Data-Flowchart-1583459854309