If two cells are the same as another row, copy paste 6 cells from that row and replace the null ones

Hi - I have an excel reader with an employee ID, department, his/her direct manager name, and the maximum amount of 6 levels of managers on top of that employee with L0 being the big boss, all the way down to that employee (L6 is the maximum). The name of the employee does not show in those levels (It stops at the direct manager name)

This excel is joined with another excel with employee IDs as well. The Idea is to attach the columns L0 to L6 for each matching employee ID.

The problem is, if the joined excel with the L0 To L6 does not have that employee ID in the first place, then L0 to L6 come out at null. This can happen with new employees and ones that have left the company.

What I need to do is, is to find a logic to automate all new names and old ones, to populate L0 to L6, because at the end of the day, teams are static and didn’t change. So in summary, the logic should say:
If L0 to L6 is empty, Insert same L0 to L6 from a row that shows same Manager Name and Department .

In the example below, it should copy Yellow in L0, Green in L1, Black in L2, Orange in L3 and Red in L4 in row2 because Manager and Department is the same as in row3. (Red and Sales)

I would appreciate any help please. thank you

You’re more likely to get help if you can supply some sample data.

Thank you! Could you please delete this post because it wont allow me. I just opened a new one that is more precise.

Hi I would like to ask for help please! - Attached is sample data. The logic should have L0 to L6 automatically copied from row 3 to row 2 , and row 4 into row 5 because of same manager and department. Is that possible? Thank you!
HCknimetest.xlsx (9.4 KB)

No need to open a new thread in this case. I combined your thread into this one to help keep the forum tidy. :slight_smile:

I was thinking my opening explanation was too complicated and wanted to simplify it because no one seems to be helping :slight_smile:

@Menoufi not sure if iI do overlook something but can’t you just make a distinct list of all Managers with their L0 to L6 and then just left join this list to the rows which do not have a L0?

The problem is I extracted the information from a software that just showed me all the active users (with their IDs), and the 6 managers going up (lets call this excel A). I then used the joiner with extracted excels from other dates (using ID as the common title). After doing so, there are IDs that were not located in excel A, therefore I need to find a way to automate whenever I have weekly excels with new IDs, to locate the 6 managerial levels using both Department, and direct manager as the rule to autopopulate those

@Menoufi I cam up with this that would extract the latest hierarchy from the entries where the L0 position is filled:


Thank you so much for this. Is it possible if you can attach the sample knime workflow you did please?

Click on the link in @mlauber71 post and download the flow

I clicked on every link and nothing would download - sorry to be annoying but I am really clueless here. Could you please show me where?

@Menoufi here is how you can download workflows from the KNIME Hub


Thank you @mlauber71. It worked perfectly!

1 Like

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.