Need Help with Logic on Employee Level

[viratsharma]

Hello,

I am trying to understand how I can set Level of Heirarchy on a column where itis a list of employees from CEO to Associates.
Associate Report to Managers
Managers report to Leaders

Leaders report to CEO

I have 2 columns, first contains names of all employees (Column Name = User)(CEO, Leader, Managers and Associates) and second columns contains who User report to (CEO, Leaders and Managers).

CEO will get Level 1
Leader gets Level 2
Manager Level 3

and Associates Level 4

These are more than 500 entries and thus I need help, please advise.

[#2]

@viratsharma 

Hi,

I think the ideal is just to find out how many layers of "Direct Report" an employee has to go through to reach "CEO".

e.g, Manager1, who reports to Leader2, who in turns reports to CEO, needs to go through 3 people to reach CEO, so Manager1's level is 3.

I implement it in Excel using some lookup formulas.

[#3]

@viratsharma 

For a technique that does not require helper columns (and can handle an arbitrary number of levels in the hierarchy), see the attached workbook.

Note: This uses the LAMBDA function, and thus requires Excel 365 or Excel for the web.

[#4]

@viratsharma 

Hi,

I made the recursive lambda slightly shorter and removed “Switch” and “Let”, and keep only one core function (XLOOKUP) to make it easier to understand:

=LAMBDA(reports_to, level, IF(reports_to = "", level, LevelsOfPerson(XLOOKUP(reports_to, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1), level + 1)))

 

I understand you might not be very enthusiastic about coding, but I still think it is worth trying to explain the lambda as clearly as possible using the example data:

We have a customised function called “LevelsOfPerson”

In order to check the level of Associate1, whose direct report is Manager6

We use  LevelsOfPerson(reports_to=“Manager6”,  level = 1)

Here is how this function is executed:

First, it checks whether “Manager6” is an empty string or, apparently, it is not.

So LevelsOfPerson will be called again.

But this time, it is called with below parameters:

reports_to=“XLOOKUP(“Manager6”, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1) = “Leader3”

level = 1 + 1 = 2

That is, LevelsOfPerson(reports_to=“Leader3”, level=2) is being executed.

Since “Leader3” is still not an empty string, so LevelsOfPerson will be called another time, with different parameters:

reports_to=XLOOKUP(“Leader3”, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1) = “CEO”

level = 2+1 = 3

So LevelsOfPerson(reports_to=“CEO”,level=3) is being executed.

As “CEO” is still not an empty string, we will call LevelsOfPerson another time, with below parameters:

reports_to=XLOOKUP(CEO”, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1) = “”

level = 3 + 1 = 4

So LevelsOfPerson(reports_to=“”,level=4) is being executed.

Finally, “” is an empty string, level = 4 will be returned. (by below IF(reports_to = "", level, ……))

IF(reports_to = "", level, LevelsOfPerson(XLOOKUP(reports_to, 'LAMBDA technique'!$A$2:$A$1001,'LAMBDA technique'!$B$2:$B$1001, "", 0,1), level + 1)