Report with Grouping

[Pestomania]

I have a long employee list (over 18000 rows) that I am trying to create a roster.

I am trying to create a roster for ALL managers, regardless of which Manager column they fall in.

I am trying to make the manager roster, grouped by downline managers.

So a report for someone listed as Mrg 3, would have their report grouped by anyone that is a Mgr 2 who reports to that person and then anyone who is Mgr 1 that reports to Mgr 2 which reports to Mgr 3.

I don't think this makes sense, but I am going to try and make a report of what this might look like.

Data File:

BCM Copy of Organziational Structure (for upload).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Org Code	user.mobile	U.S. Person	Company	Department	Site	ID	organizationalPerson.homePhone	Email	Mgr1 Name	Mgr2 Name	Mgr3 Name	Mgr4 Name	Mgr5 Name	Mgr6 Name
2	Employee 29										Manager 86	Manager 27	Manager 109	Manager 143	Manager 119
3	Employee 2										Manager 53	Manager 3	Manager 10	Manager 119
4	Employee 30										Manager 86	Manager 27	Manager 109	Manager 143	Manager 119
5	Employee 35										Manager 86	Manager 27	Manager 109	Manager 143	Manager 119
6	Employee 4										Manager 124	Manager 79	Manager 109	Manager 143	Manager 119
7	Employee 31										Manager 149	Manager 79	Manager 109	Manager 143	Manager 119
8	Employee 7										Manager 115	Manager 91	Manager 109	Manager 143	Manager 119
9	Employee 8										Manager 13	Manager 104	Manager 3	Manager 10	Manager 119
10	Employee 9										Manager 120	Manager 74	Manager 103	Manager 40	Manager 119
11	Employee 10										Manager 84	Manager 12	Manager 75	Manager 119
12	Employee 11										Manager 50	Manager 61	Manager 134	Manager 78	Manager 34	Manager 119
13	Employee 12										Manager 8	Manager 12	Manager 75	Manager 119
14	Employee 13										Manager 84	Manager 12	Manager 75	Manager 119
15	Employee 14										Manager 85	Manager 66	Manager 5	Manager 24	Manager 75	Manager 119
16	Employee 15										Manager 16	Manager 139	Manager 75	Manager 119
17	Employee 16										Manager 50	Manager 61	Manager 134	Manager 78	Manager 34	Manager 119
18	Employee 17										Manager 16	Manager 139	Manager 75	Manager 119
19	Employee 18										Manager 46	Manager 101	Manager 67	Manager 10	Manager 119
20	Employee 19										Manager 46	Manager 101	Manager 67	Manager 10	Manager 119
21	Employee 20										Manager 96	Manager 145	Manager 34	Manager 119
22	Employee 21										Manager 131	Manager 91	Manager 109	Manager 143	Manager 119
23	Employee 22										Manager 141	Manager 91	Manager 109	Manager 143	Manager 119
24	Employee 23										Manager 79	Manager 109	Manager 143	Manager 119
25	Employee 24										Manager 77	Manager 125	Manager 3	Manager 10	Manager 119
26	Employee 25										Manager 125	Manager 3	Manager 10	Manager 119
27	Employee 26										Manager 31	Manager 69	Manager 88	Manager 17	Manager 119
28	Employee 27										Manager 140	Manager 31	Manager 69	Manager 88	Manager 17	Manager 119
29	Employee 28										Manager 141	Manager 91	Manager 109	Manager 143	Manager 119
30	Employee 71										Manager 115	Manager 91	Manager 109	Manager 143	Manager 119
31	Employee 78										Manager 108	Manager 91	Manager 109	Manager 143	Manager 119
32	Employee 1										Manager 121	Manager 99	Manager 109	Manager 143	Manager 119
33	Employee 32										Manager 142	Manager 55	Manager 43	Manager 103	Manager 40	Manager 119
34	Employee 33										Manager 44	Manager 75	Manager 119
35	Employee 34										Manager 70	Manager 28	Manager 110	Manager 144	Manager 10	Manager 119
36	Employee 3										Manager 102	Manager 99	Manager 109	Manager 143	Manager 119
37	Employee 36										Manager 128	Manager 8	Manager 12	Manager 75	Manager 119
38	Employee 37										Manager 14	Manager 62	Manager 56	Manager 143	Manager 119
39	Employee 38										Manager 84	Manager 12	Manager 75	Manager 119
40	Employee 39										Manager 73	Manager 66	Manager 5	Manager 24	Manager 75	Manager 119
41	Employee 40										Manager 135	Manager 90	Manager 97	Manager 75	Manager 119
42	Employee 41										Manager 90	Manager 97	Manager 75	Manager 119
43	Employee 42										Manager 134	Manager 78	Manager 34	Manager 119
44	Employee 43										Manager 49	Manager 97	Manager 75	Manager 119
45	Employee 44										Manager 90	Manager 97	Manager 75	Manager 119
46	Employee 45										Manager 80	Manager 43	Manager 103	Manager 40	Manager 119
47	Employee 46										Manager 95	Manager 133	Manager 106	Manager 19	Manager 40	Manager 119
48	Employee 47										Manager 81	Manager 105	Manager 18	Manager 37	Manager 40	Manager 119
49	Employee 48										Manager 7	Manager 42	Manager 113	Manager 40	Manager 119
50	Employee 49										Manager 58	Manager 150	Manager 107	Manager 40	Manager 119
51	Employee 50										Manager 35	Manager 11	Manager 69	Manager 88	Manager 17	Manager 119
52	Employee 51										Manager 104	Manager 3	Manager 10	Manager 119
53	Employee 52										Manager 9	Manager 93	Manager 4	Manager 112	Manager 40	Manager 119
54	Employee 53										Manager 64	Manager 30	Manager 42	Manager 113	Manager 40	Manager 119
55	Employee 54										Manager 20	Manager 63	Manager 15	Manager 67	Manager 10	Manager 119
56	Employee 55										Manager 117	Manager 65	Manager 106	Manager 19	Manager 40	Manager 119
57	Employee 56										Manager 51	Manager 147	Manager 48	Manager 37	Manager 40	Manager 119
58	Employee 57										Manager 23	Manager 129	Manager 21	Manager 88	Manager 17	Manager 119
59	Employee 58										Manager 118	Manager 92	Manager 107	Manager 40	Manager 119
60	Employee 59										Manager 25	Manager 47	Manager 59	Manager 143	Manager 119
61	Employee 60										Manager 33	Manager 138	Manager 48	Manager 37	Manager 40	Manager 119
62	Employee 61										Manager 148	Manager 98	Manager 60	Manager 116	Manager 89	Manager 119
63	Employee 62										Manager 38	Manager 114	Manager 52	Manager 89	Manager 119
64	Employee 63										Manager 57	Manager 132	Manager 24	Manager 75	Manager 119
65	Employee 64										Manager 122	Manager 123	Manager 22	Manager 37	Manager 40	Manager 119
66	Employee 65										Manager 41	Manager 54	Manager 29	Manager 88	Manager 17	Manager 119
67	Employee 66										Manager 82	Manager 1	Manager 87	Manager 112	Manager 40	Manager 119
68	Employee 67										Manager 26	Manager 68	Manager 2	Manager 10	Manager 119
69	Employee 68										Manager 39	Manager 32	Manager 74	Manager 103	Manager 40	Manager 119
70	Employee 69										Manager 122	Manager 123	Manager 22	Manager 37	Manager 40	Manager 119
71	Employee 70										Manager 127	Manager 94	Manager 36	Manager 37	Manager 40	Manager 119
72	Employee 5										Manager 130	Manager 99	Manager 109	Manager 143	Manager 119
73	Employee 72										Manager 126	Manager 71	Manager 137	Manager 44	Manager 75	Manager 119
74	Employee 73										Manager 83	Manager 129	Manager 21	Manager 88	Manager 17	Manager 119
75	Employee 74										Manager 147	Manager 48	Manager 37	Manager 40	Manager 119
76	Employee 75										Manager 6	Manager 146	Manager 36	Manager 37	Manager 40	Manager 119
77	Employee 76										Manager 100	Manager 111	Manager 60	Manager 116	Manager 89	Manager 119
78	Employee 77										Manager 136	Manager 76	Manager 45	Manager 17	Manager 119
79	Employee 6										Manager 72	Manager 99	Manager 109	Manager 143	Manager 119
user

Similar report look:

BCM Copy of Organziational Structure (for upload).xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M
3	Employees who Report to Manager 109
4		Present	Level 3: Mgr 3 Name	Level 2: Mgr 2 Name	Level 1: Mgr1 Name	Level 0: Employees	ID	Org Code	Phone	Department	Site	Bldg	Email
5			Manager 109
6				Manager 27
7					Manager 86
8						Employee 29
9						Employee 30
10						Employee 35
11				Manager 79
12						Employee 23
13					Manager 124
14						Employee 4
15					Manager 149
16						Employee 31
17				Manager 91
18					Manager 115
19						Employee 7
20						Employee 71
21					Manager 131
22						Employee 21
23					Manager 141
24						Employee 22
25						Employee 28
26					Manager 108
27						Employee 78
28				Manager 99
29					Manager 121
30						Employee 1
31					Manager 102
32						Employee 3
33					Manager 130
34						Employee 5
35					Manager 72
36						Employee 6
Sheet1

 

[DanteAmor]

It does work with the test data

Ah ok

I wonder if it is something to do with the commas.

No, it has nothing to do with commas.

Anything else different in your data compared to the test data?

Do you have formulas, do you have empty rows, do you have cells with any errors?

 

[Pestomania]

I ran it on the initial data that I provided to you in #1, it worked properly and completely.

In the "actual" file, I have done the following:

1. Add a "Sheet1" Sheet
2. Add a "Temp" Sheet
3. Ensure the actual data is in the correct format prior to running the macro
4. Change the sheet name from "user" to "employee roster"
5. Create a button that is connected to "Global Macro"
6. Click button to run "Global Macro"

7. I attempted to run the macro with a different manager's name and it seemed to run into a different error:

I tried to put in my boss:

The current macro:

Option Explicit

Dim a As Variant, b As Variant, j As Long, k As Long
Dim dic As Object
Dim shU As Worksheet, sh1 As Worksheet, shT As Worksheet

Sub GlobalMacro()
  Set shU = Sheets("Employee_Roster")
  Set sh1 = Sheets("Sheet1")
  Set shT = Sheets("Temp")
  Dim f As Range

  Application.ScreenUpdating = False
  If sh1.Range("A3").Value = 3 Then
    MsgBox "Fill A3 cell"
    Exit Sub
  End If
  Set f = shU.Range("K:AZ").Find(sh1.Range("A3").Value, , xlValues, xlWhole, , , False)
  If f Is Nothing Then
    MsgBox "Manager does not exists"
    Exit Sub
  End If
 
  Call GetUniqueValues
  Call HierarchyManger
  Call RepaintData
  Application.ScreenUpdating = True
End Sub

Sub HierarchyManger()
  Dim i As Long, sId As Variant, dic1 As Object, col As Variant
 
  Set dic = CreateObject("Scripting.Dictionary")
  Set dic1 = CreateObject("Scripting.Dictionary")
  sh1.Range("C2", sh1.Cells(Rows.Count, Columns.Count)).ClearContents
 
  a = shT.Range("A2:D" & shT.Range("C" & Rows.Count).End(xlUp).Row).Value2
  ReDim b(1 To UBound(a, 1), 1 To 1)
  ReDim c(1 To UBound(a, 1), 1 To UBound(a, 1))
  For i = 1 To UBound(a, 1)
    dic1(a(i, 3)) = a(i, 4)
  Next
 
  j = 1
  k = 1
  sId = sh1.Range("A3")
  b(j, k) = sId
  dic(sId) = k
  k = 2
  Call recur(sId)
 
  For i = 1 To j
    col = dic(b(i, 1))
    c(i, col) = b(i, 1)
    c(i, col + 1) = dic1(b(i, 1))
  Next
  sh1.Range("C5").Resize(dic.Count, UBound(c, 2)).Value = c
End Sub

Sub recur(n)
  Dim i As Long, personID As New Collection, num As Variant
  For i = 1 To UBound(a, 1)
    If a(i, 1) = n Then
      dic(a(i, 3)) = k
      personID.Add a(i, 3)
    End If
  Next
  For Each num In personID
    j = j + 1
    b(j, 1) = num
    k = k + 1
    Call recur(num)
    k = k - 1
  Next
End Sub

Sub GetUniqueValues()
  Dim c As Variant, d As Variant
  Dim i As Long, n As Long, nRow As Long, lr As Long, lc As Long, y As Long
  Dim dic2 As Object
 
  Set dic2 = CreateObject("Scripting.Dictionary")
 
  shT.Cells.ClearContents
  shU.Range("A:A").Copy shT.Range("F1")
  lc = shU.Cells(1, Columns.Count).End(1).Column
  shU.Range("K1", shU.Cells(1, lc)).EntireColumn.Copy shT.Range("G1")
 
  lr = shT.Range("F" & Rows.Count).End(3).Row
  lc = shT.Cells(1, Columns.Count).End(1).Column
  d = shT.Range("F2", shT.Cells(lr, lc)).Value
  ReDim c(1 To lr * lc, 1 To 4)
 
  For n = 1 To UBound(d, 2)
    For i = 1 To UBound(d, 1)
      If d(i, n) <> "" Then
        dic2(d(i, n)) = Empty
      End If
    Next
  Next
 
  shT.Range("C2").Resize(dic2.Count, 1).Value = Application.Transpose(dic2.keys)
  dic2.RemoveAll
  c = shT.Range("A2", shT.Range("C" & Rows.Count).End(3)).Value
  For i = 1 To UBound(c)
    dic2(c(i, 3)) = i
  Next

  For n = 1 To UBound(d, 2) - 1
    For i = 1 To UBound(d, 1)
      If d(i, n) <> "" Then
        nRow = dic2(d(i, n))
        c(nRow, 1) = d(i, n + 1)
      End If
    Next
  Next

  With shT.Range("A2").Resize(UBound(c, 1), UBound(c, 2))
    .Value = c
    .Sort shT.Range("C2"), xlAscending
  End With
End Sub

Sub RepaintData()
  Dim c As Variant, d As Variant
  Dim f As Range
  Dim dic1 As Object
  Dim lr As Long, lc As Long, i As Long, n As Long
  Dim nRow As Long, col As Long, nCol As Long, nLevel As Long
 
  Set dic1 = CreateObject("Scripting.Dictionary")
  Set f = sh1.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious)
  lr = f.Row
  Set f = sh1.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious)
  lc = f.Column
 
  c = sh1.Range("C5", sh1.Cells(lr, lc)).Value
  d = shU.Range("A2:J" & shU.Range("A" & Rows.Count).End(3).Row).Value
 
  For i = 1 To UBound(d, 1)
    dic1(d(i, 1)) = i
  Next
 
  For n = 1 To UBound(c, 2) - 1
    For i = 1 To UBound(c, 1)
      If c(i, n) <> "" Then
        If dic1.exists(c(i, n)) Then
          c(i, UBound(c, 2)) = c(i, n)
          c(i, n) = ""
        End If
      End If
    Next
  Next
 
  ReDim e(1 To UBound(c, 1), 1 To 9)
  For i = 1 To UBound(c, 1)
    If c(i, UBound(c, 2)) <> "" Then
      If dic1.exists(c(i, UBound(c, 2))) Then
        nRow = dic1(c(i, UBound(c, 2)))
        For col = 2 To UBound(d, 2)
          e(i, col - 1) = d(nRow, col)
        Next
      End If
    End If
  Next
 
  sh1.Range("C5").Resize(UBound(c, 1), UBound(c, 2)).Value = c
  sh1.Cells(5, lc + 1).Resize(UBound(e, 1), UBound(e, 2)).Value = e
  sh1.Cells(4, lc + 1).Resize(1, 9).Value = shU.Range("B1:J1").Value
  Set f = shU.Cells.Find(sh1.Range("A3").Value, , xlValues, xlWhole, xlByColumns, xlPrevious)
 
  nCol = 3
  nLevel = f.Column - Columns("K").Column + 1
  For col = f.Column To Columns("K").Column Step -1
    sh1.Cells(4, nCol).Value = "Level " & nLevel & ": " & shU.Cells(1, col).Value
    nCol = nCol + 1
    nLevel = nLevel - 1
  Next
  sh1.Range("B4").Value = "Present"
  sh1.Cells(4, nCol).Value = "Level 0: Employees"
  sh1.Cells.EntireColumn.AutoFit
End Sub

Some sample data:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ
1	Name	Org Code	ID	Phone	Department	Site	Bldg	Email	Mgr1 Name	Company	organizationalPerson.homePhone	U.S. Person	Mgr1 ID	Mgr1 Email	Mgr2 Name	Mgr2 ID	Mgr2 Email	Mgr3 Name	Mgr3 ID	Mgr3 Email	Mgr4 Name	Mgr4 ID	Mgr4 Email	Mgr5 Name	Mgr 5 ID	Mgr5 Email	Mgr6 Name	Mgr6 ID	Mgr6 Email	Mgr7 Name	Mgr7 ID	Mgr7 Email	Mgr8 Name	Mgr8 ID	Mgr8 Email	Mgr9
2	Bread, George T	HGBBB			Business Continuity Management			george.t.bread@email.com	Norco, Silas	Spirit (Direct Employee)				silas.norco@email.com	Salt, Brain		brain.salt@email.com
3	Gert, Regal	HGBBB			Business Continuity Management			regal.l.gert@email.com	Norco, Silas	Spirit (Direct Employee)				silas.norco@email.com	Salt, Brain		brain.salt@email.com
4	Woppo, Pitty	HGBBB			Business Continuity Management			pitty.woppo@email.com	Norco, Silas	Spirit (Direct Employee)				silas.norco@email.com	Salt, Brain		brain.salt@email.com
5	Norco, Silas	HGBBB			Business Continuity Management			silas.norco@email.com	Salt, Brain	Spirit (Direct Employee)				brain.salt@email.com
Sheet1

 

[DanteAmor]

You must provide a valid manager.

You changed the structure of the main sheet:

it was the first point of my instructions:

According to your example in the "user" sheet, the managers start in column K. But now they start in column M, but you also added the name and email.
This is how the macro will never work.

I'm going to stop crying.

If you want it to work, you must put the data as you have it in your example in post #1.

If you want me to modify the macro for the new structure you are presenting, then You must adapt the data so that it has a pattern.

That is:​

Column A the employee​

Column L the manager Name​

Column M the ID ​

Column N the email​

every 3 columns a manager. for example:​

As you could see with the test data, the macro works.

 

[DanteAmor]

Or better yet:

In an "Employee Data" sheet, the name of the employee and all their data, there in that sheet you can put the data you already have and incorporate new columns for new data, for example: hobbies, schooling, address, YouTube channel, website, etc.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Org Code	ID	Phone	Dep	Site	Bldg	Email	Company	orgPh	U.S. Per	youtube	website
2	Bread, George T	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
3	Gert, Regal	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
4	Woppo, Pitty	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
5	Norco, Silas	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
employee data

On the "employee roster" sheet the hierarchy:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Name	Mgr1 Name	Mgr2 Name	Mgr3 Name	Mgr4 Name	Mgr5 Name	Mgr6 Name	Mgr7 Name	Mgr8 Name	Mgr9 Name
2	Bread, George T	Norco, Silas	Salt, Brain
3	Gert, Regal	Norco, Silas	Salt, Brain
4	Woppo, Pitty	Norco, Silas	Salt, Brain
5	Norco, Silas	Salt, Brain
employee roster

In this employee roster sheet neither the ID nor the email is necessary, because you already have those data in the "Employee Data" sheet.

That way the structure is not broken and you can incorporate all the data you want. If you need some data simply by name, look for it in column A of the "employee data" sheet and get it.

I suppose you are in the design of your sheets, think about what would be the best option for you.
Once you have solved it, come back here, tell me and I will adapt the macro to your sheets structure.

 

[Pestomania]

Or better yet:

In an "Employee Data" sheet, the name of the employee and all their data, there in that sheet you can put the data you already have and incorporate new columns for new data, for example: hobbies, schooling, address, YouTube channel, website, etc.

Dante Amor
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	Org Code	ID	Phone	Dep	Site	Bldg	Email	Company	orgPh	U.S. Per	youtube	website
2	Bread, George T	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
3	Gert, Regal	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
4	Woppo, Pitty	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
5	Norco, Silas	HGBBB			Bus cm			d@email.com	Spirit (Direct Employee)
employee data

On the "employee roster" sheet the hierarchy:

Dante Amor
	A	B	C	D	E	F	G	H	I	J
1	Name	Mgr1 Name	Mgr2 Name	Mgr3 Name	Mgr4 Name	Mgr5 Name	Mgr6 Name	Mgr7 Name	Mgr8 Name	Mgr9 Name
2	Bread, George T	Norco, Silas	Salt, Brain
3	Gert, Regal	Norco, Silas	Salt, Brain
4	Woppo, Pitty	Norco, Silas	Salt, Brain
5	Norco, Silas	Salt, Brain
employee roster

In this employee roster sheet neither the ID nor the email is necessary, because you already have those data in the "Employee Data" sheet.

That way the structure is not broken and you can incorporate all the data you want. If you need some data simply by name, look for it in column A of the "employee data" sheet and get it.

I suppose you are in the design of your sheets, think about what would be the best option for you.
Once you have solved it, come back here, tell me and I will adapt the macro to your sheets structure.

Thank you so much and you're absolutely correct! I forgot about that, I apologize for all of the confusion!

 

[Pestomania]

The second option looks much better! So I set it up like your second recommendation of "employee_roster" and "employee_data". I set the employee_roster the way you have it above but used Index - Match - Match to populate the table (rather than using a macro). I did receive the following errors still.


Formulas being used to populate:
Name =

=UNIQUE(user[Name])

All other Columns:

=IF(INDEX(user, MATCH($A2,Employee_data!$A:$A,0),MATCH(B$1,user[#Headers],0))=0,"",INDEX(user, MATCH($A2,Employee_data!$A:$A,0),MATCH(B$1,user[#Headers],0)))

Unfortunately, XL2bb keeps crashing my excel (bad computer) and cannot get the code for this screen but I made sure that all of the header fields matched the headers from the original post.

Name	Org Code	user.mobile	U.S. Person	Company	Department	Site	ID	organizationalPerson.homePhone	Email	Mgr1 Name	Mgr2 Name	Mgr3 Name	Mgr4 Name	Mgr5 Name	Mgr6 Name	Mgr7 Name	Mgr8 Name	Mgr9

 

[DanteAmor]

The second option looks much better!

Ok we are progressing.

Something very important in all this, you must follow my instructions correctly:

Once you have solved it, come back here, tell me and I will adapt the macro to your sheets structure.

Obviously the macro is not ready. You must wait for it to I make the modifications.


So according to the second recommendation you should have 2 sheets. "employee_roster" and "employee_data"

The one that interests us is the "employee_roster" sheet.
Then on the "employee_roster" sheet you must put the information as follows.

- Always using the employee's name and the manager's name.​

- The names of the managers should not appear in column A of employees.​

I will make the changes in the macro, but first confirm that the "employee_roster" sheet is as follows:

varios 11oct2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Mgr1 Name	Mgr2 Name	Mgr3 Name	Mgr4 Name	Mgr5 Name	Mgr6 Name	Mgr7 Name	Mgr8 Name	Mgr9 Name
2	Bread, George T	Norco, Silas	Salt, Brain
3	Gert, Regal	Norco, Silas	Salt, Brain
4	Woppo, Pitty	Norco, Silas	Salt, Brain
5
employee roster

Note: Check that Norco, Silas and Salt, Brain (managers) are not in column A (employees). This is very important, since this is your initial example. If not, we have serious problems.

 

[DanteAmor]

If you checked this thread and need something similar, you can find a couple of similar solutions in the following link, there you can see how to structure your data to obtain the hierarchy structure.

VBA Code to run hierarchy

Hi, I need help with VBA code so that I can enter a supervisor's ID and name and then run the macro to build a complete hierarchy for that person. Per the example below I have a list of supervisors and employees. I want to type in a supervisor's ID and name (ID "1000", Name "Person A") as o...

www.mrexcel.com

 

[Pestomania]

Ok we are progressing.

Something very important in all this, you must follow my instructions correctly:

Obviously the macro is not ready. You must wait for it to I make the modifications.


So according to the second recommendation you should have 2 sheets. "employee_roster" and "employee_data"

The one that interests us is the "employee_roster" sheet.
Then on the "employee_roster" sheet you must put the information as follows.

- Always using the employee's name and the manager's name.​

- The names of the managers should not appear in column A of employees.​

I will make the changes in the macro, but first confirm that the "employee_roster" sheet is as follows:

varios 11oct2024.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Name	Mgr1 Name	Mgr2 Name	Mgr3 Name	Mgr4 Name	Mgr5 Name	Mgr6 Name	Mgr7 Name	Mgr8 Name	Mgr9 Name
2	Bread, George T	Norco, Silas	Salt, Brain
3	Gert, Regal	Norco, Silas	Salt, Brain
4	Woppo, Pitty	Norco, Silas	Salt, Brain
5
employee roster

Note: Check that Norco, Silas and Salt, Brain (managers) are not in column A (employees). This is very important, since this is your initial example. If not, we have serious problems.

That is a tough one to accomplish since managers do report to someone. I apologize that my initial data didn't portray the details as well as I had hoped but I know that I will need to do better next time.

Would there be a way to filter out the manager being developed (let's say it is Salt, Brain) from column A while it builds the roster?

 

[DanteAmor]

If you checked this thread and need something similar

I mean if anyone else is a follower of this thread.

I apologize that my initial data didn't portray the details as well as I had hoped but I know that I will need to do better next time.

Unfortunately I can't continue helping you, the data you entered at the beginning is totally false and thus no macro will be able to work.

But without a doubt, when you have your file well organized and you provide coherent, homogeneous and approved information, then you come back here, create a new thread and I will gladly help you.