+ Reply to Thread
Results 1 to 6 of 6

Extensive Tree Structure

  1. #1
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,452

    Extensive Tree Structure

    In the thread located here http://www.excelforum.com/excel-prog...ml#post2310968 I asked for a macro that would resolve the tree structure of an organization, showing who reports to who all the way down the chain.

    The macro developed worked well for the sample data, and went 4 levels into the structure, providing output like this:
    Please Login or Register  to view this content.
    However, it couldn't cope with the full data, which has 8 or more levels of management, such as
    Please Login or Register  to view this content.
    The attached spreadsheet has the source data on Sheet 1, and on Sheet 2 a representation of what the output should look like. Any help I can get on getting to the desired output would be greatly appreciated.
    Attached Files Attached Files
    Last edited by Mordred; 08-30-2011 at 01:02 PM.

  2. #2
    Forum Expert mrice's Avatar
    Join Date
    06-22-2004
    Location
    Surrey, England
    MS-Off Ver
    Excel 2013
    Posts
    4,966

    Re: Extensive Tree Structure

    Try this macro

    Please Login or Register  to view this content.
    It's not very efficient but it should get there in the end.
    Martin

    If my solution has saved you time and/or money, please consider sponsoring my run in the 2020 London Marathon in aid of Cancer Research UK.

    https://uk.virginmoneygiving.com/MartinRice

  3. #3
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,659

    Re: Extensive Tree Structure

    The attached formats the output a little differently, but accommodates arbitrary depth. It does 10K names in about 3s.
    Attached Files Attached Files
    Last edited by shg; 05-23-2010 at 04:12 PM.
    Entia non sunt multiplicanda sine necessitate

  4. #4
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,452

    Re: Extensive Tree Structure

    Mrice,
    I'm trying your macro now. It's been running for 5 minutes, and it's up to line 3500, so I understand what you meant by "not very efficient". But I look forward to the results.

    SHG,
    Some days the network here allows me to open or save zipped files from the forum, some days it doesn't. Today it doesn't. Can I get you to post just the macro?

    Thanks to both of you for your help on this!

  5. #5
    Valued Forum Contributor
    Join Date
    12-02-2009
    Location
    Austin, Tx
    MS-Off Ver
    Excel 2016 Office 365 64-Bit
    Posts
    3,452

    Re: Extensive Tree Structure

    Martin,

    The macro bombed at this line:
    Please Login or Register  to view this content.
    with the error message
    Please Login or Register  to view this content.

  6. #6
    Forum Guru romperstomper's Avatar
    Join Date
    11-04-2008
    Location
    A1
    MS-Off Ver
    Most
    Posts
    12,302

    Re: Extensive Tree Structure

    The Sort object does not exist prior to Excel 2007, hence the error.
    Remember what the dormouse said
    Feed your head

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1