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.

Supervisor	Employee
Joe	Paul
Joe	Karen
Paul	Tom
Paul	Jerry
Karen	Tony
Karen	Mary
Karen	Terry
Karen	Sam
Mary	Andrew
Terry	Samantha

<tbody>
</tbody>

Need it to turn into...

Supervisor	Oversees Manager	Oversees Manager	Oversees Manager	Oversees Manager
Joe	Paul	Karen	Mary	Terry
Paul
Karen	Mary	Terry
Mary
Terry

<tbody>
</tbody>

 

[MickG]

I can't duplicate your results , see the example file below:-
https://app.box.com/s/1mw9q23n0g2a9qvs2f36j9xcbz3grnwn

 

[CyrusTheVirus]

I can't duplicate your results , see the example file below:-
https://app.box.com/s/1mw9q23n0g2a9qvs2f36j9xcbz3grnwn

This is VERY close, but it appears that the code executes based on a certain sort... for instance below is my actual data of how it will typically be laid out. The blank employees indicate that they are not managers. Would you be able to adjust the code for the below??

Supervisor	Employee
100
100
100
100
100
100
100
100
100
100
100
100
101
101
101
101
101
102
102	113
102
102	136
102	160
102	165
102
103
103
104
104
104
104
104
104
105
106
106
106
106
106
106
106
106
106
106
106
107
108
108
108
109	116
109	158
109	170
110
110
110
110
110
110
111
111
111
111
111
111
111
111
111
111
111
111
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
112
113
113
113
113	117
113
113
113	138
113	144
113
113	146
113
113	164
113
113
114
114
114
114
114
114
114
114
114
114
114
115
115
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
116
117
117
117
117
117
117
117
117
117
117
119
120
121
121
121
121
121
122
122
122
122
122
122
122
122
122
123
123
123
124
124
124
124
124
124
124
124
124
124
124
124
124
125
125
125
125
125
125
126
126
126
126
126
126
126
127
127
127
127
127
127
127
127
127
128
128
128
128
128
128
128
128
128
128
129
129
129
129
129
129
129
129
129
129
129
129
130
130
130
130
130
130
130
131
131
131
131
131
131
132	168
133
133
133
133
133
133	128
133
133	139
133
134	108
134
134
134
134
134	169
135
135
135
135
135
135
135
135
135
135
135
135
135
135
135
136
137
137
137
137
137
137
137
137
138	126
138	141
138	142
138	179
138	180
139
139
139
139
139
139
139
139
139
139
139
139
139
139
139
139
140
140
140
140
140
141
141
141
141
141
141
141
141
142
142
142
142
142
142
142
142
142
142
142
142
144
144
144
144
144
145
145
145
145
145
145
145
145
145
145
145
145
145
145
145
145
146	104
146	111
146	122
146	127
146	129
146	155
147	107
147
147	131
147
147
148
148
148
148
148
148
149	100
149	101
149
149	102
149	106
149	109
149
149
149
149	119
149	120
149	121
149	123
149
149	134
149
149	147
149	150
149	153
149	157
149	162
149
149
149	174
150
150
150
150
150
150
150
150
150
150
150
150
150
150
151
151
151
151
151
151
151
151
151
151
151
151
153
153
153
153
154	105
154	110
154	140
155
155
155
155
155
155
155
155
155
156
156
156
156
156
156
156
156
156
156
156
156
156
157
157
157
158
158
158
158
158
158
158
158
158
158
158
158
158
160	115
160
160	130
160
160	135
160	145
160	151
160
160
160	178
161
161
161
161
161
161
161
161
161
161
161
161
161
162
162
163
163
164	112
164	125
164	161
164	166
164	172
165	133
165	154
165	167
165	176
166
166
166	124
166
166
166	156
166
166
166
166
166
167
167	114
167
167
167
167
167	137
167
167
167
167
167
167
167
167
168
168	163
169
169
169
169	148
169
170
170
170
170
170
170
170
170
170
170
170
170
170
170
170
170
170
170
170
171
171
171
171
171
171
171
171
172
172
172
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
174
176
176
176
176
176
176
176
176
176
176	171
176
176
178
178
178
178
178
178
178
178
178
178
178
178
178
178
178
179
179
179
179
179
179
179
179
179
179
179
179
179
179
179
179
179
180
180
180
180
180
180
180
180
180
180
180
180
180

<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>
</tbody>

 

[MickG]

Try this for results starting "F1".
NB:- If this code does not produce expected result, please show example of expected result.

[COLOR=navy]Sub[/COLOR] MG01Sep54
[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] Dn.Offset(, 1) <> "" [COLOR=navy]Then[/COLOR]
   [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]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
Dim Kay As Variant, Txt As Long 
 c = 1
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] K [COLOR=navy]In[/COLOR] Dic.keys
    c = c + 1: ac = 6
    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]

Hey Mick,

Thanks for the reply and the try again. Doesn't seem to yet do it (the originally was closer). Let me work on another example for you and report back. Thanks for your patience.

Basically though, all the supervisors should be listed once in the resulting table, and the names to the right of them should be managers that they can see. Should be a cascading effect.

 

[CyrusTheVirus]

Hey Mick,

Below is an example. Though i stopped manually adding oversees managers at supervisor 134. The order of the oversees managers doesn't matter, as long as they are all there. As you see, supervisor 102 is one of the top employees, and therefore can see all managers under her, and their managers, and their managers etc. etc. etc. (I hope i correctly manually added them). Again, this is a cascading thing. Please let me know of any questions.

https://app.box.com/s/uc2yyvfi2h1hzkongamql40ejsgv0z5i

 

[MickG]

Your link is not active !!!!

 

[CyrusTheVirus]

Sorry Mick, first time using the app box, though the link works for me: https://app.box.com/s/uc2yyvfi2h1hzkongamql40ejsgv0z5i

 

[MickG]

Try this for results starting "D1"

[COLOR=navy]Sub[/COLOR] MG02Sep12
[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] Dn.Offset(, 1) <> "" [COLOR=navy]Then[/COLOR]
   [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]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
[COLOR=navy]Dim[/COLOR] nRng [COLOR=navy]As[/COLOR] Range
 c = 1
[COLOR=navy]

For[/COLOR] [COLOR=navy]Each[/COLOR] k [COLOR=navy]In[/COLOR] Dic.keys
    c = c + 1: ac = 4
    Cells(1, ac).Value = "Supervisor"
    Cells(c, ac).Value = k
   
        [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] p [COLOR=navy]In[/COLOR] Dic(k)
            ac = ac + 1
            Cells(c, ac) = p.Value
            Cells(1, ac) = "Oversees Manager"
            [COLOR=navy]If[/COLOR] Dic.exists(p.Value) [COLOR=navy]Then[/COLOR]
                [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] nRng = Dic(p.Value) Else [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dic(p.Value))
            [COLOR=navy]End[/COLOR] If
        [COLOR=navy]Next[/COLOR] p
           
        [COLOR=navy]Do[/COLOR] [COLOR=navy]While[/COLOR] Not nRng [COLOR=navy]Is[/COLOR] Nothing
             [COLOR=navy]Set[/COLOR] Rng = nRng: [COLOR=navy]Set[/COLOR] nRng = Nothing
             [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] R [COLOR=navy]In[/COLOR] Rng
                    ac = ac + 1
                    Cells(c, ac) = R.Value
                    Cells(1, ac) = "Oversees Manager"
                [COLOR=navy]If[/COLOR] Dic.exists(R.Value) [COLOR=navy]Then[/COLOR]
                    [COLOR=navy]If[/COLOR] nRng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR] [COLOR=navy]Set[/COLOR] nRng = Dic(R.Value) Else [COLOR=navy]Set[/COLOR] nRng = Union(nRng, Dic(R.Value))
                [COLOR=navy]End[/COLOR] If
             [COLOR=navy]Next[/COLOR] R
        [COLOR=navy]Loop[/COLOR]
[COLOR=navy]Next[/COLOR] k
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[CyrusTheVirus]

Mick,

I think it's official. You. Are. A. Genius.

This is what I was looking for (everything appears good). Wish I had your skillzzzzz. Thanks a lot, this is a HUGE help!!

-C

 

[MickG]

You're welcome
Glad its working for you!!