My beginning raw data is a single excel with two tabs and I created this attached workflow where I extract each tab as a separate excel with multiple tabs based on the value in the Affinity field. So now I have two separate excel files each with multiple tabs.
I am trying to create a single Excel file where I have the tabs from both excel files combined based on matching names. The field structure is different, so I just want the equivalent of having the data with fields from one tab in Excel #1 pasted underneath the matching tab name in Excel #2. If there is no match, then I still want the data pasted into a tab with that name (kind of like an outer join result).
Attached is the raw data along with a partial sample output (real output would have many more tabs, I just made three tabs manually to show).
it seems you want to have the tables under each other, as if you’d copy&paste them in Excel? (but separately for each affinity value) Easy peasy, your example worksheet helped a lot!
The task is basically a sophisticated concatenate:
get rid of the column names (to have the tables go nicely under each other)
find unique affinity values and loop over them
for both tables separately, filter by the affinity value
don’t forget the sneaky little sum of the payment column
formatting (column names, empty rows, double to string)
actual concatenation
Additional Info:
Different data types don’t go well with the concatenate node. Everything is read as String to avoid issues (except the Payment column, but that is cast to String)
Moving the columns around with a Column Resorter Node could match the few similar columns
As far as I’m aware, formatting cannot be done with the Excel Writer Node
I tried to Node Golf this into something less weird, but I think I would end up with a less resilient WF and even more nodes
