Match inputs based on tab name, group those to keep outputs combined by tab name

Question

Not sure if this is possible, but I have existing workflows that, upon output, go into Excel and 'Change File/Table Name' by a 'field containing file name or part of file name' (keep field in output is checked). Everything in that flow is working as expected and desired.

However, I also have a second workflow with slightly different data, but that is still separated by the exact same criteria above. I would now like to combine the outputs by that same Field Containing File Name.

Example:

Workflow #1 results in an Excel output with tab names of John Smith, Sara Smith, and Brian Smith.

Workflow #2 results in an Excel output (new file, different data from workflow #1) with tab names of John Smith, Sara Smith, and Brian Smith.

Desired FINAL output:

Excel output with 1 workbook per tab name - so, Workbook A would have two tabs for John Smith (John Smith 1 & John Smith2 ). Workbook B would have two tabs for Sara Smith. Workbook C would have two tabs for Brian Smith.

Thanks in advance!

ewall · Answer

@vizAlter does this only work if they have the same data on both tabs? I have the same number of columns on each, but they are different (i.e. Column B is Metric A on tab 1 for John Smith, but Column B is Metric B on tab 2 for John Smith - so they cannot be combined). I'm just getting one of the tabs' column headers on the output which is why I'm asking (and there's only 1 tab per person - John Smith only has 1 tab when I run the workflow).

vizAlter · Answer

@ewall — Try this solution for an idea:

Scenario-1) Import multiple Excel Workbooks with multiple Worksheets

Scenario-2) Output/Export multiple Excel Workbooks with multiple Worksheets

FYI - Assuming that you have similar kind of data structure for each worksheet of all the Excel workbooks:

Run this workflow inside the folder where you have all the Excel files.

Please mark this solved if it helps!

Dynamic Input Multi-Excel with Multi-Sheets— Output Separate Excel with Multi-Sheets.yxmd

File A.xlsx

File B.xlsx

ewall · Answer

Thanks @vizAlter - that definitely works up to a point, but I need to avoid creating the filter based on the names. There are too many (100+ names) to create filters for each (and isn't really a time savings over just manually creating new files based on the tab names), which is why I wanted it to auto-group based on the name containing "Brian Smith" and "John Smith".

Any other thoughts?