How can I combine tabs that have the same name from two separate excel files?

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
https://drive.google.com/drive/folders/1R-KMO0Q32TrQ7NRQBbpmrpCjppIrX858?usp=sharing

Thank you for your help. This seems like a tough nut to crack.

Hi @Shmelky,

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:

  1. get rid of the column names (to have the tables go nicely under each other)
  2. find unique affinity values and loop over them
  3. for both tables separately, filter by the affinity value
  4. don’t forget the sneaky little sum of the payment column
  5. formatting (column names, empty rows, double to string)
  6. 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 :rooster:


sophisiticated concatenate.knwf (851.2 KB)

3 Likes

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) :slight_smile: