List of Direct and Indirect Reports

[John Menken]

I have an Excel 2010 spreadsheet like the one shown in Fig 1 below. At
work I am continually asked for "group" reports which means that the
requestor wants to see everyone in so and so's group. For example, if
I get a request to see everyone in David Drew's group then the
finished report would look like Fig 2. below. A dream macro would be
one that prompts me for a Last name, lets me input a name, then
presto, places a report like Fig 2 on sheet2 of the workbook. Can
anyone help me with the code that might do something like this? Thanks
for entertaining my plight.

Fig 1.

FirstName Lastname EmpID MgrEmpID Email Location
Alex Anderson aaaa noManager [email protected] Philadelphia
Brooke Brown bbbb aaaa [email protected] Philadelphia
Carol Clark cccc aaaa [email protected] Philadelphia
David Drew dddd aaaa [email protected] Philadelphia
Erin Eisley eeee bbbb [email protected] Phoenix
Felicia Ford ffff bbbb [email protected] Phoenix
Grace Griffin gggg cccc [email protected] Phoenix
Henry Howard hhhh cccc [email protected] Phoenix
Ian Iverson iiii dddd [email protected] New Orleans
Janice Jones jjjj dddd [email protected] New Orleans
Karl King kkkk eeee [email protected] New Orleans
Linda Lewis llll eeee [email protected] New Orleans
Mike Miller mmmm ffff [email protected] Boston
Nancy Nash nnnn ffff [email protected] Boston
Olivia Ollie oooo gggg [email protected] Boston
Paula Price pppp gggg [email protected] Boston
Quentin Quincy qqqq hhhh [email protected] Cleveland
Robert Richardson rrrr hhhh [email protected] Cleveland
Steve Simmons ssss iiii [email protected] Cleveland
Tina Thomas tttt iiii [email protected] Cleveland
Ursula Urban uuuu jjjj [email protected] Houston
Veronica Valooshka vvvv jjjj [email protected] Houston
Walter Watson wwww aaaa [email protected] Houston
Xenon Xandruski xxxx wwww [email protected] Houston
Yolanda Young yyyy xxxx [email protected] Dallas
Zachary Zubov zzzz xxxx [email protected] Dallas



Fig 2

FirstName Lastname EmpID MgrEmpID Email Location
Ian Iverson iiii dddd [email protected] New Orleans
Steve Simmons ssss iiii [email protected] Cleveland
Tina Thomas tttt iiii [email protected] Cleveland
Janice Jones jjjj dddd [email protected] New Orleans
Ursula Urban uuuu jjjj [email protected] Houston
Veronica Valooshka vvvv jjjj [email protected] Houston

 

[Don Guillett]

How do we know which group each member is in?? Then, it's a simple
matter to use data>filter>autofilter>filter by group number or letter.
Send me you file if you like. dguillett1 @gmail.com

 

[John Menken]

We don't know what group any one employee is in.
What we know is that person's managerEmployeeID number.
Therefore through a recursive query we should be able to arrive at the
answer.
I used to do this with loops in a web programming language but I do
not know how to go about it in Visual Basic for Excel.
Thank you.

 

[Ron Rosenfeld]

I have an Excel 2010 spreadsheet like the one shown in Fig 1 below. At
work I am continually asked for "group" reports which means that the
requestor wants to see everyone in so and so's group. For example, if
I get a request to see everyone in David Drew's group then the
finished report would look like Fig 2. below. A dream macro would be
one that prompts me for a Last name, lets me input a name, then
presto, places a report like Fig 2 on sheet2 of the workbook. Can
anyone help me with the code that might do something like this? Thanks
for entertaining my plight.

This needs considerable "prettying up" but should get you started. It's getting late so I did no more than the basics.
Given that you want to be prompted for a name, I decided to construct a list of the last names only, and have that pop up in a List Box (User Form)

I am assuming you have some familiarity with the VBE.

Some of the assumptions:
Your main data is on Sheet1
Your report will be on Sheet2
There is nothing else of value on Sheet2
There are no duplicate last names (with different first names) to be considered.
Column F on your main data sheet has an entry for every (or at least for the last) row.

Some areas to "pretty up"
Formatting
Have OK Cancel buttons on the User Form and the associated code to allow a more elegant exit other than just closing the form with the upper right "X" button.
Have some code to make sure that autofilter is NOT enabled on Sheet1 when you run the macro, else the macro will crash.

Here is your main code which should go into a "regular" module. That is produced by ensuring your project is highlighted in the VBE; then selecting Insert/Module from the main menu bar:

========================================
Public rTable1 As Range
Public sManager As String
Option Explicit
Sub GroupReport()
Dim rReport As Range
Dim colCriteria As Collection
Dim Criteria As String, aCriteria() As String
Dim c As Range
Dim i As Long
Dim sFirstAddress As String

'assumes column F filled all the way down
With Worksheets("Sheet1")
Set rTable1 = .Range("A1", .Cells(.Rows.Count, "F").End(xlUp))
End With
Set rReport = Worksheets("Sheet2").Cells(1, 1)
Set colCriteria = New Collection

UserForm1.Show
'Selected Manager now in sManager

Criteria = rTable1.Columns(2).Find(what:=sManager, LookIn:=xlValues, _
lookat:=xlWhole).Offset(columnoffset:=1).Text

colCriteria.Add Item:=Criteria, Key:=Criteria

With rTable1.Columns(4)
Do
For i = 1 To colCriteria.Count
Set c = .Find(what:=colCriteria(i))
If Not c Is Nothing Then
sFirstAddress = c.Address
Do
'use Collection to prevent duplicates
On Error Resume Next
colCriteria.Add Item:=c.Offset(columnoffset:=-1).Text, _
Key:=c.Offset(columnoffset:=-1)
On Error GoTo 0
Set c = .FindNext(c)
Loop While Not c Is Nothing And sFirstAddress <> c.Address
End If
Next i
Loop Until i > colCriteria.Count
End With

ReDim aCriteria(1 To colCriteria.Count)
For i = 1 To colCriteria.Count
aCriteria(i) = colCriteria(i)
Next i

rTable1.AutoFilter field:=4, Criteria1:=aCriteria, _
Operator:=xlFilterValues

rReport.Worksheet.Cells.Clear
rTable1.SpecialCells(xlCellTypeVisible).Copy Destination:=rReport
rReport.Worksheet.Cells.EntireColumn.AutoFit

rTable1.AutoFilter
End Sub
=========================================

For the User Form, select Insert/User Form. A design window will open on which you will draw a User Form; and then draw a ListBox inside the user form.
The UserForm has a name of "UserForm1"
The ListBox is named "ManagerNames"

The code behind the UserForm (which you get to by right-clicking on the userform design and selecting View Code) is as below:

============================
Option Explicit
Private Sub ManagerNames_Click()
sManager = ManagerNames.Text
End Sub

Private Sub userform_initialize()
Dim ManagerList()
Dim i As Long
ManagerList = WorksheetFunction.Transpose(rTable1.Resize( _
rowsize:=rTable1.Rows.Count - 1, columnsize:=1). _
Offset(rowoffset:=1, columnoffset:=1))

For i = LBound(ManagerList) To UBound(ManagerList)
ManagerNames.AddItem ManagerList(i)
Next i

End Sub
==============================

If you've done all this correctly, when you execute the macro, the form should pop up with a box of last names of which you can select only one. Select that name and exit the box by clicking the "X" in the upper right corner. The macro then determines the filtering to use for the autofilter in order to produce your desired report. It will then clear Sheet2, write the report to sheet2, and adjust the column widths.

Finally it will turn off the autofilter for the data. This is important since if the macro starts with the autofilter enabled, it will crash.

 

[John Menken]

Wow, unreal. Thank you so much. I can't wait to try this.
I really appreciate the help.

 

[John Menken]

Just tried it.
Worked flawlessly.
I will make the improvements that you suggested.
Thank you again.

 

[Ron Rosenfeld]

Wow, unreal. Thank you so much. I can't wait to try this.
I really appreciate the help.

Let me know how it works out.

By the way, to ensure that AutoFilter is turned off before you start running the macro, insert this at the beginning of the "regular" macro:

Replace:
=======================
'assumes column F filled all the way down
With Worksheets("Sheet1")
Set rTable1 = .Range("A1", .Cells(.Rows.Count, "F").End(xlUp))
End With
=======================


===============================
'assumes column F filled all the way down
With Worksheets("Sheet1")
.AutoFilterMode = False
Set rTable1 = .Range("A1", .Cells(.Rows.Count, "F").End(xlUp))
End With
================================

You are just adding the

.AutoFilterMode = False

statement near the beginning.

 

[Ron Rosenfeld]

Just tried it.
Worked flawlessly.
I will make the improvements that you suggested.
Thank you again.

Glad to help. Thanks for the feedback.

By the way, your clear and concise description of what you wanted, along with the examples of the input and expected output, was the most important factor in allowing a solution to be crafted that suited your needs.