Update an existing column with matching pattern (like vlookup)

[Yannick_Jasper]

Hello there,

so im stuck at a project and can´t find the correct node to maintain the document order.

The scenario is as followed:

Table 1 has monthly sales values per customer.

Table 2 has the historical data from 2013 on for every month. On this excel there are formulas and formatting specialities, so that overwriting the whole file cant be done (f.e. I would loose the feature of expanding and reducing the Years to see the monthly results, otherwise seeing the yearly totals).

I was given the node “Write to Excel Template” so that i dont loose the formatting in Table 2.
But how do I setup the matching for the monthly values.
Each row in Tabel 1 represents a customer and has the SUM of sales.
Each row in Table 2 represents a customer and has the SUM of sales upon every month (inkl. totals)

Dataset is copied from the original but with random salenumbers
Example01.knar (181.0 KB)

I made a example workflow with my current state.
Thanks for any help on it.
Yannick

[thor_landstrom]

Hey @Yannick_Jasper,

If I am understanding this correctly, you want to take data from Table 1 and put it into Table 2 under the correct column name such as ‘2024-02-01’ in your case as your table 1 contains data from that month.

We can achieve this by joining the 2 data tables together on ‘Rechnungsempfänger’ which I assume is your unique IDs. Then we need to clean up the output by merging the new column to the originals location and renaming it. This will maintain the document order in your Table 2 file.

Here is picture of the modified workflow below:

Here is the output I got:

Let me know if this helps,
TL