HR Hierachy report in Excel 1

[urbanhim]

Hi, i'm hoping someone can help with a query I have... I am trying to build a Human Resources Hierachy report - Basically the report has staff names, details such as cost code, and department etc etc, and also their line manager. What I need to do is show a drill-down, starting at the top showing the hierachy for each employee...

For instance, the CEO will have 4 people reporting into him, they will have 8 people each reporting into them, the people reporting into them will have 10 people and so and so.

As an example... The key data I have to build hierachy report on is as follows (along with other fields is as follows):


Forename Surname LM Forename LM Surname
Joe Smith Paul Robinson
Dave Greedy Joe Smith
Ave Garnet Joe Smith
Margeret Simpson Doug Pitt
Steven Wheeler Malcom Tourly
Doug Pitt Graham Score
Andy Wiggins Joe Smith
Barry Charlie Freda McFarlin
Malcom Tourly Samson Bugatti
Adrian Neil Joe Smith
Farah Spoon Dave Greedy
Paul Underearth Ave Garnet
Biddy Small Paul Underearth


So I would like to produce a drill-down report that would everyone, and who reports into them, ensuring that we can make sure that everyone has a line manager, and that someway shape or form, they all eventually link into the CEO, so something like this:

Paul Robinson (CEO)
Joe Smith
Dave Greedy
Farah Spoon
Ave Garnet
Paul Undereath
Biddy Small
Andy Wiggins
Adrien Neil


Hope i've got accross what i'm trying to achieve, and will be hugely grateful for any advice on how to achieve this using Excel (i'm guessing somesort of Pivot Table might be the way forward)

Thank you!

UrbanHim
Crystal XI Report Writer
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[SkipVought]

Hi,

This kind of structure is typically a parent-child relationship. It starts at the top and points down.

An entity can be both a parent and child. The entities at the bottom of the tree are children only, having no parent-child relationship.

This structure is typical for indented bills of material.

In order to explode the tree, you need to program a solution. Typical programming code is recursive, that is portions of code call itself, drilling down passing each child entity as a parent, until reaching the bottom of the tree. It is no something that can be done with native Excel. I could be done using SQL in a query, but the query must, in effect, be recursive.

My suggestion, if you want to try the coding approch, is to post in forum707 to get some help with VBA. Do you have any programming experience?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[urbanhim]

I don't have any VBA programming experience, but i'm really glad that you have mentioned that its not something that can be done in Excel directly - I have spent the past four nights laying awake thinking of possibilties before posting this question on here! If it cannot be done without some level of programming or of course SQL querying from source data then so be it!

Thanks for taking the time to reply though, its very much appreciated!

UrbanHim
Data Analyst
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[Gavona]

Ok, I have a solution that, based on your data gives the following result
[tt]
Freda McFarlin Barry Charlie
Graham Score Doug Pitt Margeret Simpson
Paul Robinson Joe Smith Adrian Neil
Andy Wiggins
Ave Garnet Paul Underearth Biddy Small
Dave Greedy Farah Spoon
Samson Bugatti Malcom Tourly Steven Wheeler [/tt]
Is this what you want?
I will post solution details in a little while - got something else to do.



Gavin

 

[urbanhim]

You have a soloution!? That is fantastic! I look forward to your post, thank you!

And yes, how you've posted is exactly what I want, so from your example, we can see that Biddy Small reports into Paul Underearth, Paul himself reports into Ave Garnet and Ave reports into Paul Robinson! Only problem is, we have 160,000 staff split over 30 countries, but any ideas or solutions will be gratefully received!!

UrbanHim
Data Analyst
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[SkipVought]

how you've posted is exactly what I want...[/code]

Whis is NOT what you posted earlier...

so something like this:

Paul Robinson (CEO)
Joe Smith
Dave Greedy
Farah Spoon
Ave Garnet
Paul Undereath
Biddy Small
Andy Wiggins
Adrien Neil

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

1. concatenated the names.
2. Named Ranges FL & LM_FL
3. Formula...
[tt]
C2: =A2
D2: =INDEX(FL,MATCH(C2,LM_FL,0),1)
[/tt]
copy C2 down
copy D2 down & over. N/A indicates no further drill down.
[tt]
A B C D
FL LM_FL
Joe Smith Paul Robinson Joe Smith Dave Greedy Farah Spoon #N/A
Dave Greedy Joe Smith Dave Greedy Farah Spoon #N/A #N/A
Ave Garnet Joe Smith Ave Garnet Paul Underearth Biddy Small #N/A
Margeret Simpson Doug Pitt Margeret Simpson #N/A #N/A
Steven Wheeler Malcom Tourly Steven Wheeler #N/A #N/A
Doug Pitt Graham Score Doug Pitt Margeret Simpson #N/A
Andy Wiggins Joe Smith Andy Wiggins #N/A #N/A
Barry Charlie Freda McFarlin Barry Charlie #N/A #N/A
Malcom Tourly Samson Bugatti Malcom Tourly Steven Wheeler #N/A
Adrian Neil Joe Smith Adrian Neil #N/A #N/A
Farah Spoon Dave Greedy Farah Spoon #N/A #N/A
Paul Underearth Ave Garnet Paul Underearth Biddy Small #N/A
Biddy Small Paul Underearth Biddy Small #N/A #N/A
[tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[urbanhim]

I want something to show Hierarchy, where you can see who reports into who, to build like a tree, organisation chart type thing, in my original post I said "I want something like this" the word "something" suggests that what I posted was merely an example of what I wanted!

Preferably I would like each person on seperate rows, because I do have about 30 columns to contend with for each person, but given the constraints, which you eluded to earlier about needing VBA code, then obviously I am open to other solutions!

UrbanHim
Data Analyst
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[urbanhim]

I shall give what you have suggested a go, it might be a good option, so thanks for your post!

UrbanHim
Data Analyst
London

[small]What's good for a hangover?... Heavy drinking the night before!![/small]

 

[Gavona]

160,000 staff…. I think that might be a bit much. Also you won’t be doing this just once so you want an automated solution rather than this. Anyhow surely there is a degree of structure so you can break this into chunks?

This solution assumes that your hierarchy has a maximum of 4 levels below the CEO. No problem in extending though. Your (sample) data is in $A$2:$D$14 with column headings in Row 1.

First concatenate your First names and last names =Forename&” “&Surname

Column Heading Formula in Row2
A Forename
B Surname
C LM Forename
D LM Surname
E Name =A2&" "&B2
F LM (line manager) =C2&" "&D2
G Lvl4 =VLOOKUP(H2,$E$2:$F$14,2,0)
H Lvl3 copy from G
I Lvl2 copy from G
J Lvl1 copy from G
K Lvl0 =E2

Select the entire region (Ctrl-A)
Edit, Copy
Edit, PasteSpecial, Values
Edit, GoTo, Special Formulas-Errors [selects all the error values]
Edit, Delete, Shift Cells Left

Select Entire Region (Ctrl-A)
Sort Lvl2, Lvl1, Lvl0
Sort Lvl4, Lvl3

In G14 (Which is below the real data, leaving 1 blank row, but I guess it could be on another sheet)
=IF(OR(G2=G1,G2=0),"",G2)
Copy this across and down so it references the entire database
Copy, PasteSpecial to Values

In F14 a formula to identify totally blank rows:
=LEN(G69&H69&I69&J69&K69)
Copy this down

Now autofilter this new data range to show only rows with 0 in column F
Delete these rows
Remove the Autofilter


Gavin

 

[Gavona]

I just re-read

...ensuring that we can make sure that everyone has a line manager, and that someway shape or form, they all eventually link into the CEO, so something like this:

If this rather than a chart is the aim there may be a better way... But I have not thought of it!

My solution may assume that there is a 'normal' structure. I have not tested how my solution would report in a situation where A reports to B who reports to C who reports to A. But if everyone has a line manager except the CEO then that is (I reckon) the only situation where they do not all eventually report to the CEO.

Gavin

 

[Gavona]

With name in column A and LM in column B then this formula in D2 and copied down will tell you how far away from the CEO each Name is.
=IF(B2=$B$2,1,VLOOKUP(B2,myDatabase,4,0)+1)

B2 is CEO
myDatabase is A1 to Cwhatever

You also need to check that all Line managers also feature in the Name column:
in C2;
=MATCH(B2,Name,0)

Now any errors in columns D or C suggest that you don't have full info. Any circular references suggest the situation where where A reports to B who reports to C who reports to A.

By the way the whole process falls apart if you have several John Smiths - names are not really sufficiently unique for your task.


Gavin