Finding / calculating reporting level in organization/hierarchy

[griffy]

So, I have a list exported from a system containing unique employee ids (children). Each of these rows has the employee’s manager unique id (parent). One of the employees is the CEO and is to be considered level = 1

I would like a way to calculate per employee what level they are on in the organization. Is this possible in an effective way?

Have tried a solution I found before based on MATCH and INDEX, but it is cumbersome and seems to consume enormous resources to calculate and recalculated all the time.

[TABLE="width: 500"]
<tbody>[TR]
[TD]Unique child id[/TD]
[TD]Unique parent id[/TD]
[TD]level[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]88[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]99[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]44[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]55[/TD]
[TD]?[/TD]
[/TR]
</tbody>[/TABLE]

 

[Michael M]

You haven't shown us how you arrive at the calculated level for each employee and what criteria is required to do the calculation.
You might be better providing a sample of data with the end result already shown and how you arrived at that result.

 

[griffy]

the formula i have used and that finally actually give the desired outcome is; =IF(A2=1;1;1+INDEX(C:C;MATCH(B2;A:A;0))) the issue is that its heavy and there are ~45k rows in up to 15 levels.
CEO is set to ID = 1

 

[Michael M]

Have you tried reducing your INDEX/MATCH ranges.....
You say approx 45000 rows, so change the formula to

=IF(A2=1;1;INDEX(C1:C50000;MATCH(B2;A1:A50000;0))+1)

 

[griffy]

I used this instead, that worked =IF(A2=1;1;1+INDEX($C$1:$C$45000;MATCH(B2;$A$1:$A$45000;0)))
i did however get 2k rows that got level zero
could this be don in a query ?