Formula to create a hierarchy for a list of employees

[zinah]

Hi,

I have below list of employees along with their managers and what I need is to create hierarchy for each employee (row). Can anyone recommend a formula that serve my purpose?

create a hierarchy.xlsx
	A	B	C	D	E	F	G	H
1	EE ID	Manager ID	Management Level 1	Management Level 2	Management Level 3	Management Level 4	Management Level 5	Management Level 6
2	10060169	20020704
3	10178832	11535541
4	10638402	50000503
5	10652485	11595721
6	10653485	20017465
7	10673231	50000155
8	10719436	20016537
9	10719939	11545563
10	10862111	50000153
11	11090678	50000254
12	11115905	11589682
13	11149046	11588799
14	11224572	50001103
15	11262318	11535541
16	11428157	20011901
17	11483476	20017024
18	11497187	10060169
19	11506851	20020242
20	11508470	11545638
21	11512684	11497187
22	11512901	11497187
23	11513330	11497187
24	11513446	11741241
25	11514986	11611806
26	11520203	20016654
27	11535541	11540468
28	11535553	11543214
29	11535565	20016640
30	11535812	11545563
31	11535814	11543214
32	11537146	11739111
33	11537399	10060169
34	11538450	20019675
35	11538527	11595031
36	11538566	11595213
37	11538590	20009235
38	11538592	11542391
39	11538601	10653485
40	11538604	20020218
41	11538611	11548384
42	11538628	20019410
43	11538631	11540682
44	11538645	11545630
45	11538650	50001383
46	11538652	11751231
47	11538653	11541921
48	11538667	20007059
49	11538668	20008210
50	11538697	50001584
51	11538717	11542168
52	11538732	11595210
53	11538736	11595319
54	11538741	11542044
55	11538743	11595213
56	11538787	20016698
57	11538797	11595213
58	11538809	11595213
59	11538813	20016291
60	11538858	20016947
61	11538868	11595307
62	11538872	11543214
63	11538892	11540810
64	11538926	11594879
65	11538943	11543855
66	11538963	20013089
67	11538967	50000935
68	11538970	11595008
69	11538979	11688035
70	11538984	11581442
71	11538989	50002237
72	11539001	11540702
73	11539014	50001584
74	11539061	11540665
75	11539081	20016882
76	11539096	11557496
77	11539098	11595213
78	11539168	11595209
79	11539185	11544247
80	11539192	11541947
81	11539200	11542524
82	11539203	20017465
83	11539783	20020450
84	11539784	11602773
85	11539790	50000152
86	11539793	11628642
87	11539794	50001200
88	11539798	11595307
89	11540258	11539001
90	11540262	20007077
91	11540263	11540606
92	11540289	20013248
93	11540292	11542397
94	11540327	11544140
95	11540341	11545360
96	11540349	20006166
97	11540394	11595057
98	11540408	20011856
99	11540418	20006793
100	11540428	11540584
101	11540441	11542352
102	11540442	11540468
103	11540450	20016605
104	11540468	50000252
105	11540470	20005830
106	11540501	20013248
107	11540518	50000005
108	11540541	11544247
109	11540563	11540341
110	11540583	20017691
111	11540584	20000086
112	11540585	11541977
113	11540606	11540618
114	11540615	20016803
115	11540618	11538813
116	11540625	20016947
117	11540632	50001656
118	11540642	11594944
119	11540646	11542504
120	11540651	11595213
121	11540655	20000086
Sheet3

 

[Kaper]

If I understood well, such a formula copied down and right will do:

=IF(C2="","",XLOOKUP(C2,$B$2:$B$120,C$2:C$120,"",0))

Of course adjust 120 to your last row in a list.


PS in sample data there are only few employees with Level 2 filled and only one with Level 3:

row 91

11540263

11540606

11540618

20016291

 

[zinah]

If I understood well, such a formula copied down and right will do:

=IF(C2="","",XLOOKUP(C2,$B$2:$B$120,C$2:C$120,"",0))

Of course adjust 120 to your last row in a list.


PS in sample data there are only few employees with Level 2 filled and only one with Level 3:

row 91

11540263

11540606

11540618

20016291

Hi, thank you for your recommendation, I tried your formula but this is what I got, can you pls. check and see if I can fix anything?

 

[Kaper]

Whoops, I copied data with row numbers in first column. So adjust column letters to read:

=IF(B2="","",XLOOKUP(B2,$A$2:$A$120,B$2:B$120,"",0))

 

[zinah]

Whoops, I copied data with row numbers in first column. So adjust column letters to read:

=IF(B2="","",XLOOKUP(B2,$A$2:$A$120,B$2:B$120,"",0))

I believe the formula you're suggesting is not what I actually needed, if you can check the green rows of the correct hierarchy vs. what your formula is suggesting, you'll get what I actually need:

create a hierarchy.xlsx
	A	B	C	D	E	F	G	H
1	EE ID	Manager ID	Management Level 1	Management Level 2	Management Level 3	Management Level 4	Management Level 5	Management Level 6
2	10060169	20020704	50001602
3	10178832	11535541	11540468	20020704
4	10638402	50000503
5	10652485	11595721
6	10653485	20017465
7	10673231	50000155
8	10862111	50000153
9	11090678	50000254
10	11115905	11589682
11	11149046	11588799
12	11224572	50001103
13	11262318	11535541	11540468	20020704
14	11428157	20011901
15	11483476	20017024
16	11497187	10060169	20020704
17	11506851	20020242
18	11508470	11545638
19	11512684	11497187	10060169	50001602
20	11512901	11497187	10060169	50001602
21	11513330	11497187	10060169	50001602
22	11513446	11741241
23	11514986	11611806
24	11520203	20016654
25	11535541	11540468	50000252	50001602
26	11535553	11543214
27	11535565	20016640
28	11535812	11545563
29	11535814	11543214
30	11537146	11739111
31	11537399	10060169	20020704
32	11538450	20019675
33	11538527	11595031
34	11538566	11595213
35	11538590	20009235
36	11538592	11542391
37	11538601	10653485	20017465
38	11538604	20020218
39	11538611	11548384
40	11538628	20019410
41	11538868	11595307
42	11538872	11543214
43	11538892	11540810
44	11538926	11594879
45	11538943	11543855
46	11538963	20013089
47	11538967	50000935
48	11538970	11595008
49	11538979	11688035
50	11538984	11581442
51	11538989	50002237
52	11539001	11540702
53	11539014	50001584
54	11539061	11540665
55	11539096	11557496
56	11539098	11595213
57	11539168	11595209
58	11539185	11544247
59	11539192	11541947
60	11539200	11542524
61	11539203	20017465
62	11539783	20020450
63	11539784	11602773
64	11539790	50000152
65	11539793	11628642
66	11539798	11595307
67	11540258	11539001	11540702
68	11540262	20007077
69	11540263	11540606	11540618
70	11540289	20013248
71	11540292	11542397
72	11540327	11544140
73	11540341	11545360
74	11540349	20006166
75	11540394	11595057
76	11540408	20011856
77	11540418	20006793
78	11540428	11540584	20000086
79	11540441	11542352
80	11540442	11540468	50000252	50001602
81	11540450	20016605
82	11540468	50000252	20020704
83	11540470	20005830
84	11540501	20013248
85	11540541	11544247
86	11540563	11540341	11545360
87	11540583	20017691
88	11540584	20000086
89	11540606	11540618	11538813
90	11540615	20016803
91	11540618	11538813
92	11540632	50001656
93	11540642	11594944
94	11540646	11542504
95	11540651	11595213
96	11540655	20000086
97	20019965	20016291
98	50000252	20020704	50001602
99	20020704	50001602
100	Sample of correct hierarchy
101	EE ID	Manager ID	Management Level 1	Management Level 2	Management Level 3	Management Level 4	Management Level 5	Management Level 6
102	10178832	11535541	50001602	20020704	50000252	11540468	11535541
Sheet4

Cell Formulas
Range	Formula
C2:H99	C2	=IF(B2="","",XLOOKUP(B2,$A$2:$A$99,B$2:B$99,"",0))

Here's what your formula is showing vs. what the correct heirarchy is showing:

EE ID	Manager ID	Management Level 1	Management Level 2	Management Level 3	Management Level 4	Management Level 5	Management Level 6
10178832	11535541	11540468	20020704
10178832	11535541	50001602	20020704	50000252	11540468	11535541

 

[zinah]

anyone can help me pls?

 

[Peter_SSs]

Can you explain the steps in words of how you got each of those particular values in the row 103 green cells for columns B,C,D,E,F,G,H?

 

[Kaper]

Please prepare sample file with just few, may be 20 lines and "easy to track numbers" like just 10000001 to 10000020. Or describe in words how hierarchy is to be written.

I used such scheme:
main ID is in column A row x. column B is direct manager for Ax. so I look for column Bx value in column A, and if it is found in row y, I write in column Cx (higher level manager) what is in found By Then I look for Cx in column A and if it is found in row z then I write Bz in Dx (even higher level manager), then look for Dx in column A ...

 

[zinah]

Can you explain the steps in words of how you got each of those particular values in the row 103 green cells for columns B,C,D,E,F,G,H?

Hi, this is a hierarchy management, what the formula should do is taking column B (employee’s manager) and looping into the whole column to see the highest manager then update column C, then formula in column D, consider the column C and takes the below level manager under C and update, then we continue the same process in other columns till we get blank/0
Hope I could clarify and explain what I need.

 

[Kaper]

This explanation sounds as my approach. Have you changed 120 into your final row.
And let me say again that in my opinion testing will be easier with sample data - some 20, may be 30 rows as described in post #8