How to produce Grouping based on hierarchy list?

**masai_chadi** · 09-29-2006, 02:25 PM

I have a list of over 10,000 employee with 7 levels supervisor relationship from our HR system. The list looks like:

Joe reports to Mary
Sandar reports to Mary
Peter reports to Philip
Viola reports to Mary
Mary reports to David
Saly reports to Mary
Philip reports to David

I would like to produce an excel grouping or hierarchy that looks like:

David
--Mary
----Joe
----Sandra
----Saly
----Viola
--Philip
----Peter

How can I achieve this?

**mrice** · 09-30-2006, 11:48 AM

You can do this by recursion

Put the bass in column B can the subordinate in column a (with a header row) and use the following macros.

Sub GenerateHierarchy()
For N = 2 To Cells(65536, 1).End(xlUp).Row
Cells(N, 12) = Cells(N, 1)
Call GetBoss(Cells(N, 1), N)
Range(Cells(N, 4), Cells(N, Cells(N, 3).End(xlToRight).Column - 1)).Delete
Next N
End Sub

Sub GetBoss(Subordinate, RowNumber)
If Application.CountIf(Columns(1), Subordinate) <> 0 Then
Boss = Columns(1).Find(Subordinate, , xlValues, xlWhole).Offset(0, 1)
Else
Boss = False
End If

The GetBoss subroutine is called recursively until it gets to the top of the hierarchy.

Once you have the reporting lines, you can sort these to get the listing that you want.

**masai_chadi** · 09-30-2006, 02:05 PM

I tried this macro by first ensuring that the Subordinate are in Column A and Bosses in Column B. The macro appears to copy Column A value in Colum D and does not produce the hierarchy. Am I doing something wrong?

**Bryan Hessey** · 09-30-2006, 05:24 PM

Originally Posted by masai_chadi

I tried this macro by first ensuring that the Subordinate are in Column A and Bosses in Column B. The macro appears to copy Column A value in Colum D and does not produce the hierarchy. Am I doing something wrong?

Assuming your list (as shown) is in column A from A2 onwards,

in B2 put

=LEFT(A2,FIND(" ",A2)-1)

in C2 put

=MID(A2,FIND("reports to ",A2)+11,99)

and formula-fill the pair to the end of your data.
(note, if you have the names in separate columns you can (obviously) omit those)

In D1 put David (the boss as shown)

In D2 put '--

or whichever character(s) you would use to offset the levels and formula-fill that downwards

in E2 put

=IF(ISNA(MATCH($C2,D$1:D$8,0)),IF(D2="--",D2,""),$B2)

and formula-fill that as far right as required, then bulk-formula-fill as far down as required. (see attached)

Make your chart by concatenating the cells D- onwards.

hth
---

**masai_chadi** · 10-04-2006, 10:48 AM

MRice, the approach you have provided will work very well for me. However, I am having a bit of difficult in that the macro only seems to copy the subordinates once on column D, and does not generate the hierarchy view. I believe this might a simple fix but I am do not know how to code. Please assist. Thank you.

**Bryan Hessey** · 10-05-2006, 02:34 AM

Originally Posted by masai_chadi

I have a list of over 10,000 employee with 7 levels supervisor relationship from our HR system. The list looks like:

Joe reports to Mary
Sandar reports to Mary
Peter reports to Philip
Viola reports to Mary
Mary reports to David
Saly reports to Mary
Philip reports to David

I would like to produce an excel grouping or hierarchy that looks like:

David
--Mary
----Joe
----Sandra
----Saly
----Viola
--Philip
----Peter

How can I achieve this?

In the attached StaffB.xls, is the green portion the correct solution that you are seeking?

note I amended Saly for testing and added others

---

**masai_chadi** · 10-05-2006, 10:48 AM

Thank you, Bryan.

**Bryan Hessey** · 10-05-2006, 03:20 PM

Originally Posted by masai_chadi

Thank you, Bryan.

If that is correct I will add a button to generate the formulae, the sort-key generation specifically is a little unwieldy for manual setting.

Can I presume that the names you have are as per column A, or are they in the form of the displayed column B and C

Do ANY names contain spaces (asin 'Mary Ann reports to Sarah Jane') etc?

---

**masai_chadi** · 10-05-2006, 03:55 PM

Originally Posted by Bryan Hessey

If that is correct I will add a button to generate the formulae, the sort-key generation specifically is a little unwieldy for manual setting.

Can I presume that the names you have are as per column A, or are they in the form of the displayed column B and C

Do ANY names contain spaces (asin 'Mary Ann reports to Sarah Jane') etc?

---

I appreciate your offer, and would like to take it up.

Actually, Column A will contain the Employee Numbers of the Subordinates and Column B will contain the Employee Numbers of the Manager. Employee Numbers are unique to the employee. (I used names for ease of read).

Once the right hierarchical results are produce I can then lookup and append other HR related information such as Position, Salary, etc.

**Bryan Hessey** · 10-05-2006, 05:50 PM

Originally Posted by masai_chadi

I appreciate your offer, and would like to take it up.

Actually, Column A will contain the Employee Numbers of the Subordinates and Column B will contain the Employee Numbers of the Manager. Employee Numbers are unique to the employee. (I used names for ease of read).

Once the right hierarchical results are produce I can then lookup and append other HR related information such as Position, Salary, etc.

Could be a little difficult, but lets try.

I have not amended columns A B and C except to remove B & C to their own button, do not press the B & C button, it will wipe columns B & C

In the Staff sheet, copy your ident to column B and the superior to column C

In row 1 replace David with whoever tops the tree.

Press the blue button

(? before sorting?) you need to take columns M and W (the only 2 needed) back to your original sheet, as the hierarchy and the sort key (paste special = values or (irrespective of sort) you can use a VLookup on the id number,False etc)

Possibilities are:
That these working columns are pushed far to the right (out of sight) or are simply hidden from view.
That the copy from & back to your sheet is automated or macro-driven.

Your sheet will need to be re-sorted (state header row) after each staff inclusion, this may be inconvenient if you have a purpose for a different sequence (alphabetic or numeric is usual).

Personally I would keep this hierarchy as a seperate sheet, you can add more formula as per the code that is shown for the button (it's pretty simple if you know the quote rule) to pickup any required information.

See how you fare
---