• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Get direct and indirect reports from employee - manager table.

C

caralva

New Member
Hello,
I have a data set for my organization.
Column A, employee #
Column B, name
Column C, Supervisor
Columd D, Supervisor #
this data goes all the way from bottom of org up to VP.
Example:
Emp# Name Supervisor
123 Bob Y Rick Y 444
111 Jim Z Rick Y 444
222 Tim A Rick Y 444
444 Rick Y Tessa A 555
555 Tessa A Mary B 888
666 Dru F Mary B 888
777 John Q Mike A 999
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? I'd prefer formulas to do this as I'm not that great in VB.?

Note: 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 or Tesssa at a Director level, id get all the people that report to them, and their reports, and their reports...and so on in a comprehensive list.
Thanks for your help!!!
Carlos
 
Let's say you input name of person in G1 though some method. Formula to generate list of reports:
=IFERROR(INDEX(B:B,SMALL(IF(C$1:C$100=$G$1,ROW(C$1:C$100)),ROW($A1))),"")

Confirm this formula as an array with Cntrl+Shift+Enter, not just Enter. Copy the formula down then as far as would ever be needed.

How it works:
http://chandoo.org/wp/2011/11/18/formula-forensics-003/


EDIT:
Just now figured out what you meant by direct/indirect reports. Scratch what I said above. I would like to know how big of a group you are dealing with...could get quite lengthy. How I would do it is still use the formula structure above, but then next column over looks at first column, first cell, pulls all those. Then 3rd column looks at first column, 2nd cell, etc. Keep building down and to the right for # of layers you need to see.
 
Yup, its great for pulling first level but not drilling down!.
the org is about 10k and will have about 8-10 levels total from VP down to the last Pawn

I tried doing the 2nd column with this formula, but no worky ;-(
is this the a way to have it look for all values in column H,

=IFERROR(INDEX(B:B,SMALL(IF($C:$C=$H:$H,ROW($C:$C)),ROW($A1))),"")
Also keep getting a resource issue and calculations not being made. anyway to aleviate that?

thanks so much for your help on this!!!
Carlos
 
As you've discovered, doing a large array comparison is going to suck up resources. Hence why I was hoping to avoid calling out the entire column. What you wrote above requires a calculation of 1,048,576 x 1,048,576 cells :oops: as it's comparing each cell in col C with each cell in col H. Yikes!

Looking around, I found this template by Chandoo:
http://chandoo.org/wp/2008/04/15/generate-organization-charts-in-excel-howto/

Creates a visual layout, but perhaps it will be helpful?
 
You must log in or register to reply here.
Back
Top