Organizational Hierarchy Issue Vlookup Look Efficient?

[mrmmickle1]

Having a little issue with something at work. I'm trying to get the "hierarchy level" of an employee based on two fields: EE Name and EE Supervisor.

I have been able to complete the task in a logical manner but am having a little trouble automating it. I was wondering if anyone has any insight on how to go about automating this process. In order to see the logical progression I will post the steps in order. The data starts off with a boss on top. This is the guy that all employees will roll up to. He is defined as Level 0.

Start:

Excel 2010
	A	B	C
1	EE Name	EE Supervisor	Level
2	John	Head Honcho	0
3	Matt	John
4	Jerry	Matt
5	Mathias	John
6	Jonesy	Matt
7	Tripp	Mathias
8	Taylor	Jonesy
9	Riley	Jonesy
10	Sarah	Meaghan
11	Meaghan	John
Sheet1

Step 1: Identify EE's that role up to John. If #N/A is returned we know they do not....

Excel 2010
	A	B	C
14	EE Name	EE Supervisor	Level
15	John	Head Honcho	0
16	Matt	John	John
17	Jerry	Matt	#N/A
18	Mathias	John	John
19	Jonesy	Matt	#N/A
20	Tripp	Mathias	#N/A
21	Taylor	Jonesy	#N/A
22	Riley	Jonesy	#N/A
23	Sarah	Meaghan	#N/A
24	Meaghan	John	John
Sheet1

Cell Formulas
Range	Formula
C16	=VLOOKUP(B16,$A$15,1,FALSE)
C17	=VLOOKUP(B17,$A$15,1,FALSE)
C18	=VLOOKUP(B18,$A$15,1,FALSE)
C19	=VLOOKUP(B19,$A$15,1,FALSE)
C20	=VLOOKUP(B20,$A$15,1,FALSE)
C21	=VLOOKUP(B21,$A$15,1,FALSE)
C22	=VLOOKUP(B22,$A$15,1,FALSE)
C23	=VLOOKUP(B23,$A$15,1,FALSE)
C24	=VLOOKUP(B24,$A$15,1,FALSE)

Step 2: Sort the Values By the level field:

Excel 2010
	A	B	C
26	EE Name	EE Supervisor	Level
27	John	Head Honcho	0
28	Matt	John	John
29	Mathias	John	John
30	Meaghan	John	John
31	Jerry	Matt	#N/A
32	Jonesy	Matt	#N/A
33	Tripp	Mathias	#N/A
34	Taylor	Jonesy	#N/A
35	Riley	Jonesy	#N/A
36	Sarah	Meaghan	#N/A
Sheet1

Cell Formulas
Range	Formula
C28	=VLOOKUP(B28,$A$15,1,FALSE)

Step 3: Replace Hits with next sequential level. In this case 1

Excel 2010
	A	B	C
38	EE Name	EE Supervisor	Level
39	John	Head Honcho	0
40	Matt	John	1
41	Mathias	John	1
42	Meaghan	John	1
43	Jerry	Matt	#N/A
44	Jonesy	Matt	#N/A
45	Tripp	Mathias	#N/A
46	Taylor	Jonesy	#N/A
47	Riley	Jonesy	#N/A
48	Sarah	Meaghan	#N/A
Sheet1

Cell Formulas
Range	Formula
C43	=VLOOKUP(B43,$A$15,1,FALSE)
C44	=VLOOKUP(B44,$A$15,1,FALSE)
C45	=VLOOKUP(B45,$A$15,1,FALSE)
C46	=VLOOKUP(B46,$A$15,1,FALSE)
C47	=VLOOKUP(B47,$A$15,1,FALSE)
C48	=VLOOKUP(B48,$A$15,1,FALSE)

Step 4: Delete #N/A Values

Excel 2010
	A	B	C
50	EE Name	EE Supervisor	Level
51	John	Head Honcho	0
52	Matt	John	1
53	Mathias	John	1
54	Meaghan	John	1
55	Jerry	Matt
56	Jonesy	Matt
57	Tripp	Mathias
58	Taylor	Jonesy
59	Riley	Jonesy
60	Sarah	Meaghan
Sheet1

Step 5: Repeat Process by changing the table array. This time we look for hits in a table array that contains level 1 employees....

Excel 2010
	A	B	C
62	EE Name	EE Supervisor	Level
63	John	Head Honcho	0
64	Matt	John	1
65	Mathias	John	1
66	Meaghan	John	1
67	Jerry	Matt	Matt
68	Jonesy	Matt	Matt
69	Tripp	Mathias	Mathias
70	Taylor	Jonesy	#N/A
71	Riley	Jonesy	#N/A
72	Sarah	Meaghan	Meaghan
Sheet1

Cell Formulas
Range	Formula
C67	=VLOOKUP(B67,$A$64:$A$66,1,FALSE)
C68	=VLOOKUP(B68,$A$64:$A$66,1,FALSE)
C69	=VLOOKUP(B69,$A$64:$A$66,1,FALSE)
C70	=VLOOKUP(B70,$A$64:$A$66,1,FALSE)
C71	=VLOOKUP(B71,$A$64:$A$66,1,FALSE)
C72	=VLOOKUP(B72,$A$64:$A$66,1,FALSE)

Step 2 Again Sort.....

Excel 2010
	A	B	C
75	EE Name	EE Supervisor	Level
76	John	Head Honcho	0
77	Matt	John	1
78	Mathias	John	1
79	Meaghan	John	1
80	Tripp	Mathias	Mathias
81	Jerry	Matt	Matt
82	Jonesy	Matt	Matt
83	Sarah	Meaghan	Meaghan
84	Taylor	Jonesy	#N/A
85	Riley	Jonesy	#N/A
Sheet1

Cell Formulas
Range	Formula
C80	=VLOOKUP(B80,$A$64:$A$66,1,FALSE)
C81	=VLOOKUP(B81,$A$64:$A$66,1,FALSE)
C82	=VLOOKUP(B82,$A$64:$A$66,1,FALSE)
C83	=VLOOKUP(B83,$A$64:$A$66,1,FALSE)
C84	=VLOOKUP(B84,$A$64:$A$66,1,FALSE)
C85	=VLOOKUP(B85,$A$64:$A$66,1,FALSE)

Step 3 Again Replace "hits" with next sequential number

Excel 2010
	A	B	C
87	EE Name	EE Supervisor	Level
88	John	Head Honcho	0
89	Matt	John	1
90	Mathias	John	1
91	Meaghan	John	1
92	Tripp	Mathias	2
93	Jerry	Matt	2
94	Jonesy	Matt	2
95	Sarah	Meaghan	2
96	Taylor	Jonesy	#N/A
97	Riley	Jonesy	#N/A
Sheet1

Cell Formulas
Range	Formula
C96	=VLOOKUP(B96,$A$64:$A$66,1,FALSE)
C97	=VLOOKUP(B97,$A$64:$A$66,1,FALSE)

Any help getting this process automated would be much appreciated. I have been having trouble figuring out:

How to change the Table Array in the Vlookup Formula within a loop and then also how to signify to stop... This would occur when all Vlookup results are #N/A or all levels are completed. The #N/A scenario can occur if employees report outside of the list I have...The organization is very large so I'm doing this process on thousands of EE's for a large amount of departments. The end goal is to outline by level which I have already been able to successfully code: http://www.mrexcel.com/forum/excel-questions/907185-create-dynamic-outline-multiple-levels-based-outline-hierarchy.html

 

[Dryver14]

Wow how did it get so messy in the first place, this seems very complicated. I would have thought copy and pasting the relevant names into a hierarchy tree would make more sense. the formulas only refer to three cells in sheet one which has miraculously generated three 1's or am I missing something, what does the actual base data look like?

 

[SpillerBD]

Why not a Pivot Table?
From your data you only need the EE Supervisor and EE Name.
Using only the Rows in a Pivot Table, assign "EE Supervisor" and then below that "EE Name"
Using either Filter or Slicer you can filter to any specific supervisor.

 

[mrmmickle1]

Dryver14,

The 1's do appear miraculously. I typed them in ahahah I figured I can evaluate cells for "#N/A" and assign the values depending on the number loop I am currently on using a counter.

SpillerBD,

Pivot table is messy. It doesn't group like a heirarchy. Its also difficult to get the additional fields to line up adjacently...even in tabular format. The real format looks something like this... I wanted to simplify it, especially sense I have already completed a few different portions of the project. here is the order of fields:

Excel 2010
	AA
4	EE Name
5	EE GUID
6	Supervisor Name
7	Employment Status
8	EE Organization Dept
9	EE Organization Sub Department
10	EE Organization Level 1
11	EE Organization Level 2
12	EE Organization Level 3
13	EE Organization Level 4
14	EE Organization Level 5
15	EE Organization Level 6
16	EE Location
17	Full . Part Time
18	Additonal Field 1
19	Additional Field 2
20	Additional Field 3
21	Additional Field 4
22	Additional Field 5
23	Additional Field 6
24	Additional Field 7
25	Additional Field 8
26	Additional Field 9
27	Additional Field 10
Sht1

 

[SpillerBD]

I might be able to do a better Pivot with complete sample of data. (I'll pm you in a bit.)
I do See that "EE Organizational Level..." 1,2,3 etc. may create some bit of problem, but I don't see any other potential issues on the surface.

 

[Peter_SSs]

I might be able to do a better Pivot with complete sample of data. (I'll pm you in a bit.)

Please refer to #4 of the Forum Rules

 

[Peter_SSs]

I got a bit confused with all those tables in post #1, but is a macro solution acceptable?
If so, does this do what you want?

Sub Employee_Level()
    Dim aEmp As Variant, aLev As Variant
    Dim sTemp As String
    Dim i As Long, Lev As Long
    Dim d As Object
    
    aEmp = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
    ReDim aLev(1 To UBound(aEmp), 1 To 1)
    Set d = CreateObject("Scripting.Dictionary")
    d.CompareMode = 1
    For i = 1 To UBound(aEmp)
      d.Add aEmp(i, 1), aEmp(i, 2)
    Next i
    For i = 1 To UBound(aEmp)
      Lev = 0
      sTemp = d(aEmp(i, 1))
      Do Until sTemp = "Head Honcho" Or sTemp = ""
        Lev = Lev + 1
        sTemp = d(sTemp)
      Loop
      If sTemp = "" Then
        aLev(i, 1) = "N/A"
      Else
        aLev(i, 1) = Lev
      End If
    Next i
    Range("C2").Resize(UBound(aLev)).Value = aLev
End Sub

For original data in columns A:B, the above code produced column C.

Excel Workbook
	A	B	C
1	EE Name	EE Supervisor	Level
2	John	Head Honcho	0
3	Matt	John	1
4	Jerry	Matt	2
5	Mathias	John	1
6	Jonesy	Matt	2
7	Tripp	Mathias	2
8	Taylor	Jonesy	3
9	Riley	Jonesy	3
10	Sarah	Meaghan	2
11	Sue	Fred	N/A
12	Meaghan	John	1
13	Colin	Sue	N/A
Level

 

[Dryver14]

So would each employees details be in AB AC etc?

 

[mrmmickle1]

Peter_SSs,

Wow, I just tested this out and it works like a charm. This is incredible! I have never used: CreateObject("Scripting.Dictionary") is there anyway that you could go a little more in depth into what the above code is doing? And perhaps expand on what the CreateObject("Scripting.Dictionary") is commonly used for? I have seen Hiker95 use this in some of his code before, have never examined it thoroughly. This is impressive thank you!

Dryver14,

All of the details go through AB. The format is in a table which is exported from BI software in .xlsx format.

 

[Peter_SSs]

Peter_SSs,

Wow, I just tested this out and it works like a charm.

Glad it worked for you.

I have never used: CreateObject("Scripting.Dictionary") is there anyway that you could go a little more in depth into what the above code is doing? And perhaps expand on what the CreateObject("Scripting.Dictionary") is commonly used for?

Here is one place you can read a bit more about Dictionaries.

A dictionary is similar to a standard book dictionary we would use at home. The main "words" (can be numbers, text etc) in the dictionary are called keys. The "definitions" of those keys are called Items.

My code really just does a series of 'lookups' like you were trying to do but first it creates the dictionary. That is just put all the column A values as keys into the dictionary and for each one the definition (Item) is that person's supervisor. So it's just like the original table in the worksheet.

Take Jonesy as an example.
- Look up Jonesy in the dictionary and get his 'definition' (Matt).
- If that is "Head Honcho" & your are finished and at level 0.
- However, that is not the case so now look up that first 'definition' (Matt) in the dictionary and increase the Level to 1.
- Matt's definition is 'John'. Not Head Honcho so increase Level to 2 then ...
- Look up John's definition & get "Head Honcho" so finished, with a Level of 2

Take Colin as another example
- definition is Sue, Level = 1
- definition of Sue is Fred, Level = 2
- look up Fred. Can't find Fred in the dictionary so must be outside the hierarchy. Change Level to N/A