Grouping staff hierarchy in xls
Hi,
I have an xls file with ~120000 records. Each record shows the name of an employee and their manager. It also shows the "Tier" within the organisation. I would like to create a formula that allows me to count all the reports that sum up in a hierarchical tree of a "Tier-1" manager.
I attached an example file.
Hope you can help me out!
Rgds,
Peter
How about a simple Vlookup..
Formula:
Please Login or Register to view this content.
Regards!
=DEC2HEX(3563)
If you like someone's answer, click the star to give them a reputation point for that answer...
Hi,
Not sure how that would count the number of reports. In my example file, John has three reports: 1 direct (Richard) and 2 indirect (Jeff and Mike). How to count them (especially the indrect reports..)
Thanks for your quick reply btw!
I dont think its that simple, Debraj.
Hi Pdk and welcome to the forum
do you mean you want to "count" the *string* for John?
Mike reports to Jeff who reports to Richard who reports to John? so 3 reports?
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
100% spot on!Originally Posted by FDibbins
I dont think its that simple, Debraj.
Hi Pdk and welcome to the forum
do you mean you want to "count" the *string* for John?
Mike reports to Jeff who reports to Richard who reports to John? so 3 reports?
Not sure how many tiers you will have, this works for 5.
Based on your sample, copy this to D2:H10...
=IFERROR(INDEX($B3:$C$10,MATCH(C2,$B3:$B$10,0),2),"-")
Then in I3, copied down...
=COUNTIF(C2:H2,"<>-")
Thank you for the quick reply FDibbins!
Unfortunately, I think, this does not work well when there are multiple people reporting directly into a certain Tier (as attached example). Correct? I think there are no more than 6 tiers to cover, but some have more then 100 direct reports.
Nice challenge....but I have no clue how to tackle this
Last edited by PdK77; 09-17-2013 at 04:24 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks