Thread: Recursive Hierarchy Problem Thanks: 0 Likes: 0

1. Recursive Hierarchy Problem

I currently have two columns of ids, employee and supervisor, and need to find what division the employee rolls up to based upon the division's Vice President. I initially have the VP ID and Division in two columns on the same sheet.
If the initial supervisor is not found on the VP "table", then the code should loop to check the supervisor's supervisor until found, or leave blank if not found after 10 tries.
Here is a sample of data. The ID and Supervisor ID columns are actually over 10,000 lines long and all employees should roll up to a division.

 ID SUPERVISOR ID Division VP ID Division 1845 2698 Intl 9312B MAC 2698 9312B 2698 Intl 300014 92659 327884 HC 300055 91713 335131 IT 300063 402662 359593 Communications 300095 94727 371834 HR/Facilities 300154 305630 373378 HIE 300171 325645 392686 LG 300206 90468 396189 CDNS 300207 302218 398236 MK/BD 300208 302218 400403 FA 300315 361952 404129 HS 300364 92272 300370 92659 300427 361952 300603 397404 300606 90818

I believe the logical recursive pseudo-code is below, the syntax is just killing me:

Start Loop for all empl ids
Get SupvID(CurrentEmplID)
DivisionFlag = False
counter = 1

Start Loop until DivisionFlag = True OR counter = 10
Check SupvID on VP Table
If on table
put Division in 3rd column of CurrentEmplID
DivisionFlag = True
Else If counter = 10
3rd column of CurrentEmplID = "No Rollup"
Else
TempEmplID = SupvID
Get SupvID(TempEmplID)
counter = counter + 1
End Loop

Increase CurrentEmplID
End Loop for all empl ids

THANKS!

2. Re: Recursive Hierarchy Problem

A solution that does not require macros is explained here: William Entriken: Excel bug/feature: recursive VLOOKUP for hierarchy calculations

3. Re: Recursive Hierarchy Problem

Your link finds the level an employee is within an organization. We need to be able to find the manager the employee rolls up to. Thanks for trying.

4. Re: Recursive Hierarchy Problem

Originally Posted by echeech
Your link finds the level an employee is within an organization. We need to be able to find the manager the employee rolls up to. Thanks for trying.
This approach will solve any hierarchy problem. The shown solution is basically: "if this is the top level, return zero, otherwise return manager +1". You are looking for "if this is a VP, return their name, otherwise return the manager's VP name".

Cheers.

5. Re: Recursive Hierarchy Problem

There's a workbook that illustrates two ways of doing this, one with macros and one with formulas, at https://www.box.com/s/lw3lfk71kbc121ac768o