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 (e-mail address removed) Philadelphia
Brooke Brown bbbb aaaa (e-mail address removed) Philadelphia
Carol Clark cccc aaaa (e-mail address removed) Philadelphia
David Drew dddd aaaa (e-mail address removed) Philadelphia
Erin Eisley eeee bbbb (e-mail address removed) Phoenix
Felicia Ford ffff bbbb (e-mail address removed) Phoenix
Grace Griffin gggg cccc (e-mail address removed) Phoenix
Henry Howard hhhh cccc (e-mail address removed) Phoenix
Ian Iverson iiii dddd (e-mail address removed) New Orleans
Janice Jones jjjj dddd (e-mail address removed) New Orleans
Karl King kkkk eeee (e-mail address removed) New Orleans
Linda Lewis llll eeee (e-mail address removed) New Orleans
Mike Miller mmmm ffff (e-mail address removed) Boston
Nancy Nash nnnn ffff (e-mail address removed) Boston
Olivia Ollie oooo gggg (e-mail address removed) Boston
Paula Price pppp gggg (e-mail address removed) Boston
Quentin Quincy qqqq hhhh (e-mail address removed) Cleveland
Robert Richardson rrrr hhhh (e-mail address removed) Cleveland
Steve Simmons ssss iiii (e-mail address removed) Cleveland
Tina Thomas tttt iiii (e-mail address removed) Cleveland
Ursula Urban uuuu jjjj (e-mail address removed) Houston
Veronica Valooshka vvvv jjjj (e-mail address removed) Houston
Walter Watson wwww aaaa (e-mail address removed) Houston
Xenon Xandruski xxxx wwww (e-mail address removed) Houston
Yolanda Young yyyy xxxx (e-mail address removed) Dallas
Zachary Zubov zzzz xxxx (e-mail address removed) Dallas



Fig 2

FirstName Lastname EmpID MgrEmpID Email Location
Ian Iverson iiii dddd (e-mail address removed) New Orleans
Steve Simmons ssss iiii (e-mail address removed) Cleveland
Tina Thomas tttt iiii (e-mail address removed) Cleveland
Janice Jones jjjj dddd (e-mail address removed) New Orleans
Ursula Urban uuuu jjjj (e-mail address removed) Houston
Veronica Valooshka vvvv jjjj (e-mail address removed) 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.

 

[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.