Create List of Employees Under Each Manager

[mcgonma]

I have a list of about 38,000 employees, and in that list is a column of who each employee's Manager is. I want to generate a list of all Managers and identify the employees under them. For example, if Susan Smith (manager) has four employees, I would want to generate a list the employees under Susan.

Susan Smith
employee A
employee B
employee C
employee D​

The column headings of the data are:

• Column A = Employee Name
• Column B = Employee ID#
• Column C = Employee Email Address
• Column D = Employee Level
• Column E = Employee’s Manager Name
• Column F = Employee’s Manager ID#

 

[Nine Zero]

Perhaps a pivot table would be good for this

 

[mcgonma]

I'll bet it would be awesome! I just wish I knew how to use them. Any guidance would be incredibly appreciated!

 

[Nine Zero]

Alright begin by highlighting all of your data including the headers

In the ribbon on top go to INSERT

Click on Pivot Table

In the pop up window your range should already be selected, simply click OK

Now in the right side of the screen you will see PivotTable Fields

Select and drag the Manager Name to the Filter Area below

And finally select and drag Employee Name to the Rows Area below

and just simply select the manager you would like to view

Let me know if you need anything else

 

[Rick Rothstein]

If you are still interested in a macro, this one should work. Simply change my example Sheet3 (assigned to DataSheet variable) and Sheet4 (assigned to OutSheet variable) sheet names to your actual sheet names for your data sheet and the sheet to be outputted to.

[table="width: 500"]
[tr]
	[td]Sub ListEmployeeNamesPerManager()
  Dim X As Long, DataSheet As String, OutSheet As String, Dict As Object
  Dim EmpName As Variant, ManName As Variant, Employees As Variant, Manager As Variant
  DataSheet = "Sheet3"
  OutSheet = "Sheet4"
  EmpName = Sheets(DataSheet).Range("A2", Sheets(DataSheet).Cells(Rows.Count, "A").End(xlUp))
  ManName = Sheets(DataSheet).Range("E2", Sheets(DataSheet).Cells(Rows.Count, "E").End(xlUp))
  Set Dict = CreateObject("Scripting.Dictionary")
    For X = 1 To UBound(ManName)
      Dict(ManName(X, 1)) = Dict(ManName(X, 1)) & EmpName(X, 1) & ";"
    Next
    X = 0
    For Each Manager In Dict.Keys
      Employees = Split(Dict(Manager), ";")
      X = X + 1
      With Sheets(OutSheet).Cells(1, X)
        .Value = Manager
        .Font.Bold = True
        .Borders(xlEdgeBottom).Weight = xlThick
      End With
      Sheets(OutSheet).Cells(2, X).Resize(UBound(Employees) + 1) = Application.Transpose(Employees)
    Next
    Sheets(OutSheet).Range("A1").Resize(, Cells(1, Columns.Count).End(xlToLeft).Column).EntireColumn.AutoFit
End Sub[/td]
[/tr]
[/table]

 

[mcgonma]

I appreciate you providing the code, but I have no idea what to do with it.

 

[Rick Rothstein]

I appreciate you providing the code, but I have no idea what to do with it.

HOW TO INSTALL MACROs
------------------------------------
If you are new to macros, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the code I posted in Message #5 into the code window that just opened up. That's it.... you are done. To use the macro, go back to the worksheet with your data on it and press ALT+F8, select the macro name (ListEmployeeNamesPerManager) from the list that appears and click the Run button. The macro will execute and perform the action(s) you asked for. If you will need to do this again in this same workbook, and if you are using XL2007 or above, make sure you save your file as an "Excel Macro-Enabled Workbook (*.xlsm) and answer the "do you want to enable macros" question as "Yes" or "OK" (depending on the button label for your version of Excel) the next time you open your workbook.

 

[mcgonma]

Thank you, Rick, for being so patient and helpful. I'm not sure what I'm doing wrong, but I think I done busted your great work. I was hoping to attach a screen shot of the error screen I receive when I try to run the macro but I can't seem to manage to find that functionality either.. Sorry to be a pain.

 

[Peter_SSs]

Rick's code worked for me. What version of Excel and what operating system are you using?

What might your maximum number of managers be?

What might your maximum number of employees for a single manager be?

Perhaps you could tell us what the full error message is?

 

[Rick Rothstein]

Rick's code worked for me. What version of Excel and what operating system are you using?

What might your maximum number of managers be?

What might your maximum number of employees for a single manager be?

Perhaps you could tell us what the full error message is?

In addition to Peter's questions above, I am wondering if you remembered to do this which I posted in Message #5 ...

"Simply change my example Sheet3 (assigned to DataSheet variable) and Sheet4 (assigned to OutSheet variable) sheet names to your actual sheet names for your data sheet and the sheet to be outputted to."

If you are unsure of what that means, then just tell us the name of the sheet your data is on and then tell us the name of the sheet you want your manager/employee listing to go on and I will physically change the code and repost it for you.

If, on the other hand, you had remembered to change the sheet names on your own, then make sure to answer Peter's questions above.