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

 

[mrmmickle1]

Peter_SSs,

Thanks so much for the thorough explanation! I appreciate you taking the time to explain in greater depth.

 

[Peter_SSs]

You're welcome.