mrmmickle1
Well-known Member
- Joined
- May 11, 2012
- Messages
- 2,461
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:
Step 1: Identify EE's that role up to John. If #N/A is returned we know they do not....
Step 2: Sort the Values By the level field:
Step 3: Replace Hits with next sequential level. In this case 1
Step 4: Delete #N/A Values
Step 5: Repeat Process by changing the table array. This time we look for hits in a table array that contains level 1 employees....
Step 2 Again Sort.....
Step 3 Again Replace "hits" with next sequential number
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
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
