Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Recursive Hierarchy Problem

  1. #1
    New Member
    Join Date
    Sep 2009
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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.

    IDSUPERVISOR IDDivisionVP IDDivision
    18452698Intl9312BMAC
    26989312B2698Intl
    30001492659327884HC
    30005591713335131IT
    300063402662359593Communications
    30009594727371834HR/Facilities
    300154305630373378HIE
    300171325645392686LG
    30020690468396189CDNS
    300207302218398236MK/BD
    300208302218400403FA
    300315361952404129HS
    30036492272
    30037092659
    300427361952
    300603397404
    30060690818


    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. #2
    New Member
    Join Date
    Nov 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #3
    New Member
    Join Date
    Sep 2009
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #4
    New Member
    Join Date
    Nov 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Recursive Hierarchy Problem

    Quote Originally Posted by echeech View Post
    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. #5
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    19,626
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com