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

[ewall]

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!

[vizAlter]

Hi @ewall — FYI, user cannot assign the exact same name to more than one worksheet. Worksheet name must be unique in MS Excel.

[ewall]

@vizAlter -doh. Let me revise - I just want to group the outputs where they match on the inputs, but I'm open to renaming them as needed - so John Smith1 John Smith2 would be okay.

[vizAlter]

@ewall — Try this solution, it will give you an idea:

Make sure that you configure each "Input Data" and "Dynamic Input" tool as below:

and

Please mark this solved if it helps.

[ewall]

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?

[vizAlter]

@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!

[ewall]

@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).