I want to make new column with Logic?

2

Given: Employee ID and Manager ID.

| Employee ID | Manager ID |
|:-----------:|:----------:|
| E068        | E067       |
| E071        | E067       |
| E229        | E069       |
| E248        | E144       |
| E226        | E223       |
| E236        | E241       |
| E066        | E001       |
| E067        | E001       |
| E144        | E001       |
| E223        | E001       |

Problem Statement:

This problem is to identify the Head of Manager by using Employee and their Manager data.

About: We have an Employee ID and their Manager ID. Please note that Manager ID are from Employee ID. Since each manager has one Manager above their level.

  1. First, we'll take all UNIQUE ID in Manager ID column.
  2. Then for each ID from Manager ID column, we will look for their respective Manager ID(Manager)
  3. Then we will create a new column say Level 1 we will put manager for each Manager ID on their respective cell.
  4. Similarly, we will repeat the above 3 processes again till there is no Manager ID for that particular ID.
  5. This way we can identify the Head of Manager.

I am able to solve the problem by filtering the excel but it takes a lot of time. Hence, I am looking for an optimal solution that can help me out.

Expected Output:

| Employee ID | Manager ID | Level 1 | Level 2 | Head of Manager |
|:-----------:|:----------:|---------|---------|-----------------|
| E068        | E067       | E001    |         | E001            |
| E071        | E067       | E001    |         | E001            |
| E229        | E069       | E066    | E001    | E001            |
| E248        | E144       | E001    |         | E001            |
| E226        | E223       | E001    |         | E001            |
| E236        | E241       |         |         | E241            |
| E066        | E001       |         |         | E001            |
| E067        | E001       |         |         | E001            |
| E144        | E001       |         |         | E001            |
| E223        | E001       |         |         | E001            |

The Employee ID column contain UNIQUE ID while Manager ID contain DUPLICATES ID.

Thank you for your time and consideration.

Maqsud Inamdar

Posted 2019-11-08T09:44:36.063

Reputation: 71

Does the given table show the expected result or what you already got? – dirdi – 2019-11-08T09:52:02.640

@dirdi Yes, And thanks for your query I have updated my question now. – Maqsud Inamdar – 2019-11-08T09:56:25.333

How many levels do you expect maximum in your hierarchy? – Máté Juhász – 2019-11-08T10:00:22.030

@MátéJuhász It depends upon the organization. As in some, there would be 4 hierarchy then Head of that department. Well, in this case, there are 2 Level – Maqsud Inamdar – 2019-11-08T10:04:28.273

Answers

2

You can use VLOOKUP for identifying manager's manager, need to create as many columns as many levels you have in your hierarchy.

=IFERROR(VLOOKUP(B2,$A:$B,2,FALSE),"")

enter image description here

Máté Juhász

Posted 2019-11-08T09:44:36.063

Reputation: 16 807

Thank you @MátéJuhász this is working – Maqsud Inamdar – 2019-11-08T10:18:20.993

Asked: 2019-11-08T09:44:36.063

Viewed: 57 times

Active: 2019-11-08T10:10:02.247