I'll admit it... I'm stumped!! Need hierarchy help.

[CyrusTheVirus]

This is the first time in my professional career that I am unable to figure out how to use features/formulas (i've never needed VBA, until maybe now) in order to solve a problem.

Basically i need to create a certain formatted hierarchical structure based on the below table. I need each supervisor to be listed in their parent's list horizontally (like the 2nd table below). If you see Joe, he oversees Paul and Karen who over see other managers, but i only want managers listed if they in fact oversee employees. For instance, Tom and Jerry don't oversee anyone, so they will be excluded from the list.

I've heard this is near impossible to do with formulas, and would be much easier be done by VBA. I'm at a loss... so, who can help with this one? Please let me know of any questions.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Supervisor[/TD]
[TD]Employee[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Paul[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Karen[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Tom[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]Jerry[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Tony[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Mary[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Terry[/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Sam[/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD]Andrew[/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD]Samantha[/TD]
[/TR]
</tbody>[/TABLE]


Need it to turn into...

[TABLE="width: 500"]
<tbody>[TR]
[TD]Supervisor[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Paul[/TD]
[TD]Karen[/TD]
[TD]Mary[/TD]
[TD]Terry[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD]Mary[/TD]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Mary[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Terry[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[RasGhul]

Hi Cyrus,

This is extracting each employee across columns per each supervisor, is this ok?

Book1
	A	B	C	D	E
1	Supervisor	Employee
2	Joe	Paul
3	Joe	Karen
4	Paul	Tom
5	Paul	Jerry
6	Karen	Tony
7	Karen	Mary
8	Karen	Terry
9	Karen	Sam
10	Mary	Andrew
11	Terry	Samantha
12
13
14	Supervisor	Oversees Manager	Oversees Manager	Oversees Manager	Oversees Manager
15	Joe	Paul	Karen
16	Paul	Tom	Jerry
17	Karen	Tony	Mary	Terry	Sam
18	Mary	Andrew
19	Terry	Samantha
Sheet1

Cell Formulas
Range	Formula
B15	{=IFERROR(INDEX($B$2:$B$11,SMALL(IF($A$2:$A$11=$A15,ROW($A$2:$A$11)-ROW($A$2)+1),COLUMNS($B$15:B15))),"")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Special-K99]

This is an interesting problem. I may not come back to this but am thinking about it...

UPDATE: Oops! Too late!

 

[CyrusTheVirus]

Hi Rasghul,

Unfortunately that does not work. I'm looking for only managers. Your table should match my second. Please refer to my explanation and let me know of any questions. I wish it were that simple.

 

[CyrusTheVirus]

Hey Special, fyi this hasn't yet been answered, so if you want to give it a shot, good luck and i'll appreciate an answer. i've beat my head against the wall for 4 hours and cannot come up with a formulaic solution. I THINK VBA is necessary, but not certain.

 

[Leith Ross]

Hello CyrusTheVirus,

Hierarchies are built on relationships between the members. There needs to be information specific to each member before a hierarchy can be established. Your post does not have enough information to clearly define the relationships. In all hierarchies ther is a starting point or root. Here it is assumed but not demonstrably shown to be Joe. The relationships must be clearly defined and not assumed for the map to be to realized.

 

[MickG]

Try this Based on your Data in "A & B", for results starting "C1".

[COLOR=navy]Sub[/COLOR] MG31Aug29
[COLOR=navy]Dim[/COLOR] Rng         [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Dn          [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] c           [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Dic         [COLOR=navy]As[/COLOR] Object
[COLOR=navy]Dim[/COLOR] R           [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
   [COLOR=navy]If[/COLOR] Not Dic.exists(Dn.Value) [COLOR=navy]Then[/COLOR]
         Dic.Add (Dn.Value), Dn.Offset(, 1)
    [COLOR=navy]Else[/COLOR]
        [COLOR=navy]Set[/COLOR] Dic(Dn.Value) = Union(Dic(Dn.Value), Dn.Offset(, 1))
    [COLOR=navy]End[/COLOR] If
[COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]Dim[/COLOR] ac [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] K [COLOR=navy]As[/COLOR] Variant, p [COLOR=navy]As[/COLOR] Variant, rep [COLOR=navy]As[/COLOR] Range
[COLOR=navy]Dim[/COLOR] Kay [COLOR=navy]As[/COLOR] Variant, Txt [COLOR=navy]As[/COLOR] [COLOR=navy]String[/COLOR]

ac = 4: c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] Dic.keys
    c = c + 1: ac = 3
    Cells(1, ac).Value = "Supervisor"
    Cells(c, ac).Value = K
    Kay = K
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] p [COLOR=navy]In[/COLOR] Dic(Kay)
            [COLOR=navy]If[/COLOR] Dic.exists(Kay) [COLOR=navy]Then[/COLOR]
                [COLOR=navy]Set[/COLOR] rep = Dic(Kay)
                    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] rep
                        Txt = R.Value
                        [COLOR=navy]If[/COLOR] Dic.exists(Txt) [COLOR=navy]Then[/COLOR]
                            ac = ac + 1
                            Cells(c, ac) = Txt
                            Cells(1, ac) = "Oversees Manager"
                        [COLOR=navy]End[/COLOR] If
                        Kay = Txt
                    [COLOR=navy]Next[/COLOR] R
             [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] p
[COLOR=navy]Next[/COLOR] K
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[CyrusTheVirus]

Thanks Mick,

But I'm only getting the below data... i need the data in the second table in my example. Could you modify this??

[TABLE="width: 286"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Supervisor[/TD]
[TD]Oversees Manager[/TD]
[TD]Oversees Manager[/TD]
[/TR]
[TR]
[TD]Joe[/TD]
[TD]Paul[/TD]
[TD]Karen[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Karen[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[CyrusTheVirus]

Hello CyrusTheVirus,

Hierarchies are built on relationships between the members. There needs to be information specific to each member before a hierarchy can be established. Your post does not have enough information to clearly define the relationships. In all hierarchies ther is a starting point or root. Here it is assumed but not demonstrably shown to be Joe. The relationships must be clearly defined and not assumed for the map to be to realized.

Hey Leith,

what if we made a separate cell contain the name (i will be using employee id's as unique identifiers, but for this example it is much easier for names) of the top dog?

 

[RasGhul]

Cyrus,

Yes you will need to have a criteria that ranks or identifies the relationships between supervisors or expand on your source table that shows the difference in employees.