I have to create a column that identifies a person’s 2nd-level manager based on hierarchy.
I have columns named ManLev01Band through ManLev11Band that contain a number followed by some letters. Example: 5trxn. The first character is all that matters here.
What I need to do is figure out the first leader in each employee’s hierarchy that contains a band less than 6. The catch here is it needs to be at least two levels above that person’s own position in the hierarchy.
Here is an example of the data with the Result column being the one that I want to generate:
| Employee Name | ManLev02Band | ManLev03Band | ManLev04Band | ManLev05Band | ManLev06Band | ManLev07Band | ManLev08Band | ManLev09Band | ManLev10Band | ManLev11Band | Result |
|---|---|---|---|---|---|---|---|---|---|---|---|
| John | 2yiol | 3koli | 4jkli | 5zvd | 6wehe | 7werf | 5zvd | ||||
| Jake | 2yiol | 3koli | 4jkli | 5zvd | 4jkli | ||||||
| Jen | 2yiol | 3koli | 4jkli | 3koli |
Note that for all scenarios, the first non-blank value is the first level leader. We are looking for the second level position or greater depending on the band level #.
For John, the result is band 5zvd. It is the third band available so it meets the criteria of at least 2nd level leader. 6wehe does not qualify because it is not <= band 5.
For Jake, while band 5zvd qualifies on the first criteria of being <= band 5, it is not a second-level leader. They are a 1st level. So we go onto the next.
With Jen the second level leader is a band 3 that meets the criteria of <= band 5. So the result for her is 3koli.