Org Data - Hierarchy Drill Down???

[caralva]

Hello,

I have a data set for my organization.
Column A, employee #
Column B, name
Column C, Supervisor

this data goes all the way from bottom of org up to VP.

Example:
Emp# Name Supervisor
123 Bob Y Rick Y
111 Jim Z Rick Y
222 Tim A Rick Y
444 Rick Y Tessa A
555 Tessa A Mary B
666 Dru F Mary B
777 John Q Mike A

I'd like to know if there is a way using formulas to get a report that would give me all of the people that report under a certain individual including the indirect reports. So if I was to select a director, it would give me all managers, supervisors, leads, etc... under that director. so in the case above, if I picked Mike A I would get back John Q but if I Picked Mary B, Id get back everyone else.

I've looked for a few hrs in the forum and not sure this has been asked before.
The data doesnt need to be ordered, but just need a list of names.
any suggetsions? or does this have to be done with VB?

Thanks for your help!!!


Carlos

[Tony Valko]

Are the supervisor's listed in ascending order?

In your example Mike A is the highest level supervisor followed by Mary B followed by Tessa A followed by Rick Y ?

If you chose Tessa A then you'd want to list employees 444, 222, 111 and 123?

[caralva]

Tony,
that is exactly it!!!

if I was to choose Mary B, id see 444,222,111,123 and Tessas number 555. It would drill all the way down

[Tony Valko]

Hmmm...

After reading the post again I don't think I follow the logic of the hierarchy.

I was thinking that all you needed to do was to find the bottom-most instance of the supervisor in column C then return all the employee numbers in column A from that row upwards.

[shg]

There's a workbook with formula- and VBA-based methods to do this at https://app.box.com/s/lw3lfk71kbc121ac768o

[caralva]

shg - great spreadsheet but when I filter for a name, it only keeps that name. I could look at the indents of the sheet but its tough to see the right level or indent and fear may throw more people into a segment than should be there.

in the calculation tab, im not following what its doing, as I don't see how it lists all the direct reports but will keep looking at it that looks more like what I'm after

thanks for the sheet. I've learned from it!
Carlos

[caralva]

SHG,
also in that spreadsheet, I have people with the same name and different employee number and it keeps erroring on me.....with circular reference. not sure why I could not find the circ ref as the person is only listed once and their supervisor doesn't have duplicate listings either.... ;-(

Carlos

[shg]

The IDs must be unique; names are fluff.

[caralva]

Tony,

Thats correct, its not about the sequence of the cells but about fishing out all of the direct and indirect reports under a certain manager at whatever level I pick... so if I pick Mary at a VP level of Tesssa at a Director level, id get all the people that report to them, and their reports, and their reports...and so on.

[Tony Valko]

Not sure this is what you had in mind but you can try it...

Data Range

	A	B	C	D	E	F
1	Emp#	Name	Supervisor	-----	Supervisor	Employee #
2	123	Bob Y	Rick Y		Mary B	123
3	111	Jim Z	Rick Y			111
4	222	Tim A	Rick Y			222
5	444	Rick Y	Tessa A			444
6	555	Tessa A	Mary B			555
7	666	Dru F	Mary B			666
8	777	John Q	Mike A

This formula entered in F2:

=IFERROR(INDEX(A$2:INDEX(A$2:A$8,LOOKUP(2,1/(C$2:C$8=E$2),ROW(C$2:C$8)-ROW(C$2)+1)),ROWS(F$2:F2)),"")

Copy down until you get blanks.

[shg]

In the workbook I linked to, if you run the macro, you can then filter the Report Chain column for Contains 00740 to see Betty Hoshaw and all of her direct and indirect reports.

	G	H	I	J
3	Employee Name	EmpID	Supervisor Name	Reporting Chain by ID
51	******Hoshaw, Betty L	00740	Oskvarek, John J	01240|00511|00767|00740
52	********Moore, Brittany L	00361	Hoshaw, Betty L	01240|00511|00767|00740|00361
53	********Howse, Renee C	00859	Hoshaw, Betty L	01240|00511|00767|00740|00859
54	**********Thomas, Christine B	00100	Howse, Renee C	01240|00511|00767|00740|00859|00100
55	**********Meisenburg, Candace L	00209	Howse, Renee C	01240|00511|00767|00740|00859|00209
56	**********Rehberg, Jennifer M	00221	Howse, Renee C	01240|00511|00767|00740|00859|00221
57	**********Rowan, Andrea L	00456	Howse, Renee C	01240|00511|00767|00740|00859|00456
58	**********Menge, Tracey A	00564	Howse, Renee C	01240|00511|00767|00740|00859|00564
59	**********Morley, Diane M	00568	Howse, Renee C	01240|00511|00767|00740|00859|00568
60	**********Goff, Lisa I	00794	Howse, Renee C	01240|00511|00767|00740|00859|00794
61	**********Loucks, Karl A	00813	Howse, Renee C	01240|00511|00767|00740|00859|00813
62	**********List, James A	00944	Howse, Renee C	01240|00511|00767|00740|00859|00944
63	**********Nguyen, Maily T	00966	Howse, Renee C	01240|00511|00767|00740|00859|00966
64	********Sather, Joan M	00866	Hoshaw, Betty L	01240|00511|00767|00740|00866
65	**********Noonan, Joan L	00103	Sather, Joan M	01240|00511|00767|00740|00866|00103
66	**********Anderson, Lynn M	00269	Sather, Joan M	01240|00511|00767|00740|00866|00269
67	**********Barker, Nina M	00560	Sather, Joan M	01240|00511|00767|00740|00866|00560
68	**********Nash, Kristin M	00744	Sather, Joan M	01240|00511|00767|00740|00866|00744
69	**********Nemmers, Stephanie H	00748	Sather, Joan M	01240|00511|00767|00740|00866|00748
70	**********Neese, Bridget M	00999	Sather, Joan M	01240|00511|00767|00740|00866|00999
71	**********Mendez, Sarah M	01140	Sather, Joan M	01240|00511|00767|00740|00866|01140
72	**********Chester, Tiffany A	01251	Sather, Joan M	01240|00511|00767|00740|00866|01251
73	********Carlson, Lori A	01232	Hoshaw, Betty L	01240|00511|00767|00740|01232

[caralva]

Tony,
I think there is an additional complexity. in the example i placed, the org was organized. I have about 10k people in the org so its not organized like the small example I gave you ;-) sorry thats my bad! i was trying to illustrate how it needed to look up the number, not necesarily based on the order of how the names appear in the org data. This data set will come to me monthly, and no way to tell if it will be in the same order every time.



Thanks so much for helping with this, i havent cracked it yet

Carlos

[Tony Valko]

I didn't think I was on the right track.

So, I guess I don't understand what you're trying to do.

Maybe shg has the solution.

Good luck!

[caralva]

SHG,

Tried using your spreadsheet and I have all unique EMP Ids but some Duplicate employee names...when I hit run it returns the "Duplicate Employee Name" field.

Also, with only having the supervisor name in spreadsheet, how does it know wich supervisor its referig to if two have the same name?

the sheet with your data works flawlessly, but with mine,,,i cant get it to move ahead

thanks once again guys!!!


Carlos

[shg]

Ah, I see -- it needs a mod to use the employee ID as the key. Dunno why I didn't do that to begin with.

If it otherwise works, you could add the employee's middle initial or full middle name to distinguish.

[caralva]

I dont have additional info regarding names.
If you can change the macro/code to list supID and read from that it would likely work best.

I'm unable to get it to work as is ;-(

Carlos