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).
Link to files
Thank you for your help. This seems like a tough nut to crack.
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
- 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
Wow! This is amazing! Thank you so much!
My bad, I think I got it all working. Can ignore previous comment I made (which I just deleted)