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

 

[Peter_SSs]

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.

That is another general description of the overall process (which I am not fully understanding). What I asked for was

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?

That is,

• explain what you do to get 11535541 in cell B103?
• explain what you do to get 50001602 in cell C103
• explain what you do to get 20020704 in cell D103
• etc

You also used the expression "looping into the whole column to see the highest manager". What is meant by "highest manager"?

 

[zinah]

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

Hi @Kaper thank you so much for your valuable input and great support, I was able to get the exact formula that can serve my purpose, I used LAMBDA formula that did the magic and created the manager hierarchy of each employee. I'm sharing this here so everyone can get the knowledge

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

Cell Formulas
Range	Formula
C3:D3,C120:D120,C113:D113,C111:E111,C107:D107,C103:E103,C101:F101,C99:D99,C90:F90,C88:D88,C38:D38,C32:E32,C26:F26,C20:F22,C17:E17,C14:G14,C5:C13,C15:C16,C18:C19,C23:C25,C27:C31,C33:C37,C39:C87,C89,C91:C98,C100,C102,C104:C106,C108:C110,C112,C114:C119,C121	C3	=TEXTSPLIT(MID(getBoss(A3,empRange,bossRange),2,999),"|")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
bossRange	='Sheet3 (3)'!$B$3:$B$121	C3:C121
empRange	='Sheet3 (3)'!$A$3:$A$121	C3:C121

 

[zinah]

That is another general description of the overall process (which I am not fully understanding). What I asked for was

That is,

• explain what you do to get 11535541 in cell B103?
• explain what you do to get 50001602 in cell C103
• explain what you do to get 20020704 in cell D103
• etc

You also used the expression "looping into the whole column to see the highest manager". What is meant by "highest manager"?

Thank you for your feedback and help @Peter_SSs what I'm trying to do is to create manager hierarchy for each employee. The sample result I shared has no formula, it's a manual copy/paste that's why I needed a formula. I was able to find the formula and shared it here so everyone can take advantage and use the same if they have the same issue as I did. Thanks once again!

 

[Peter_SSs]

what I'm trying to do is to create manager hierarchy for each employee.

Yes, you had already said that. What I was asking for was a description in words of how you would manually get the values listed in the green cells. I would still be interested in that.

I was able to find the formula and shared it here so

.. though unfortunately you have not really shared because we don't have the LAMBDA function that you used.

If you update your XL2BB version to the latest version (see my signature block below) then you can include LAMBDA functions in the Mini Sheet data

 

[zinah]

Yes, you had already said that. What I was asking for was a description in words of how you would manually get the values listed in the green cells. I would still be interested in that.


.. though unfortunately you have not really shared because we don't have the LAMBDA function that you used.

If you update your XL2BB version to the latest version (see my signature block below) then you can include LAMBDA functions in the Mini Sheet data

View attachment 108759

I will download the new version later, thanks for highlighting this! As for the LAMBDA, here's the formula:

=LAMBDA(employee,empRange,bossRange,LET(boss,XLOOKUP(employee,empRange,bossRange,""),IF(boss="","",getBoss(boss,empRange,bossRange)&"|"&boss)))

 

[zinah]

Yes, you had already said that. What I was asking for was a description in words of how you would manually get the values listed in the green cells. I would still be interested in that.


.. though unfortunately you have not really shared because we don't have the LAMBDA function that you used.

If you update your XL2BB version to the latest version (see my signature block below) then you can include LAMBDA functions in the Mini Sheet data

View attachment 108759

Here's the full sheet with the LAMBDA formula:

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

Cell Formulas
Range	Formula
C3:D3,C120:D120,C113:D113,C111:E111,C107:D107,C103:E103,C101:F101,C99:D99,C90:F90,C88:D88,C38:D38,C32:E32,C26:F26,C20:F22,C17:E17,C14:G14,C5:C13,C15:C16,C18:C19,C23:C25,C27:C31,C33:C37,C39:C87,C89,C91:C98,C100,C102,C104:C106,C108:C110,C112,C114:C119,C121	C3	=TEXTSPLIT(MID(getBoss(A3,empRange,bossRange),2,999),"|")
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
bossRange	=solution!$B$3:$B$121	C3:C121
empRange	=solution!$A$3:$A$121	C3:C121

Lambda Functions
Name	Formula
getBoss	=LAMBDA(employee,empRange,bossRange,LET(boss,XLOOKUP(employee,empRange,bossRange,""),IF(boss="","",getBoss(boss,empRange,bossRange)&"|"&boss)))

 

[Peter_SSs]

Here's the full sheet with the LAMBDA formula:

Great, thanks for that. The reason I was asking for description of how you got the values was that the results didn't make sense to me as I was imagining that Management Level 1 was the lowest level but with your function I can now see it means the highest level. So I was looking at the sample results in the wrong order.

As a matter of interest, another way to deal with that leading "|", instead of the MID(...,2,999) structure would be to use this formula in the worksheet

=DROP(TEXTSPLIT(getBoss(A3,empRange,bossRange),"|"),,1)

 

[zinah]

Great, thanks for that. The reason I was asking for description of how you got the values was that the results didn't make sense to me as I was imagining that Management Level 1 was the lowest level but with your function I can now see it means the highest level. So I was looking at the sample results in the wrong order.

As a matter of interest, another way to deal with that leading "|", instead of the MID(...,2,999) structure would be to use this formula in the worksheet

=DROP(TEXTSPLIT(getBoss(A3,empRange,bossRange),"|"),,1)

Thank you so much for your great suggestion, I see that your formula is straightforward and easier to digest and remember, I love this platform and the learning experience I get with every question and answers I receive! Thanks for everyone (@Kaper ) who support here and suggested solutions!

 

[Peter_SSs]

You're welcome.

Given the type of data you have, another option would be to use a space as the delimiter in your LAMBDA instead of the "|" character ..

=LAMBDA(employee,empRange,bossRange,LET(boss,XLOOKUP(employee,empRange,bossRange,""),IF(boss="","",getBoss(boss,empRange,bossRange)&" "&boss)))

.. then in the worksheet use

=TEXTSPLIT(TRIM(getBoss(A3,empRange,bossRange))," ")

BTW, I have changed the 'Mark as solution' to your post since that is the one with the solution to the original question.