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



Fig 2

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