+ Reply to Thread
Results 1 to 3 of 3

Convert hierarchy list to flat table format

  1. #1
    Registered User
    Join Date
    12-11-2015
    Location
    Texas
    MS-Off Ver
    MS Office 2013
    Posts
    18

    Exclamation Convert hierarchy list to flat table format

    I am trying to convert the following hierarchy list to a flat table format. Before each name there are spaces. The greater the spaces before the name the further down the hierarchy they are. Hard to see in the sample list below. (See attached Excel for better view)

    SISCO CONSOLIDATED
    STG CONSOLIDATED
    STC TOTAL
    GLENN CLEMENTS STC GROUP
    STC EASTERN OP
    STC EASTERN OP-GUY KRUGER
    STC EASTERN OP-72331
    STC EASTERN OP-06.72331
    STC EASTERN OP-99.72331
    STC EASTERN OP-SANDY CAPELL
    STC EASTERN OP-72327
    STC EASTERN OP-53.72327
    STC EASTERN OP-99.72327
    ETC......................

    How would I convert this into a Table format where I have multiple columns each representing the different parent/child levels...Column one will be the highest level so "SISCO CONSOLIDATED" then next column would have all the children, column 3 would be the associated children of column 2, etc...etc...

    Attached below is the actual Excel file with the complete list of all the offices/ and parent offices. (See below). I think this can be solved with VBA, but I am not sure.

    Thanks so much!!
    Attached Files Attached Files

  2. #2
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Re: Convert hierarchy list to flat table format

    I used a manual approach because there is much to be gleaned from the steps.
    I also attached a workbook where each column is another progression in the steps.
    (To keep the file small, I converted it to an XLSB file and converted most of the formulas to values.
    Formulas have red text. If you follow the steps on your file, you should see the same results)

    A
    B
    2
    Instructions
    3
    1
    Calc number of preceding spaces
    4
    2
    Convert PreSpaces to units (4 each), ignoring 1st
    5
    3
    Replace PreSpaces with pipe characters |
    6
    4
    Convert that column to values
    7
    5
    Run Text-to-columns, using the pipe as a delimiter
    8
    6
    Put heading labels for Level_1 through Level_12
    9
    7
    Select blank cells in the Text-to-columns destination range
    10
    • Home.Find&select.Go_To...Click: Special cells...Check: Blanks...Click: OK
    11
    • H2 will be the active cell
    12
    8
    Enter this formula in H2 and press CTRL+ENTER, to automatically propagate that formula in every selected cell
    13
    9
    =IF(LEFT(H1,5)="level","",IF(COLUMNS($G:H)<=($D2+1),H1&"",""))
    14
    15
    The end result is what you want....convert all of the formulas to values OR Copy/Paste_Special.Values to another tab

    Is that something you can work with?
    Attached Files Attached Files
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  3. #3
    Registered User
    Join Date
    12-11-2015
    Location
    Texas
    MS-Off Ver
    MS Office 2013
    Posts
    18

    Re: Convert hierarchy list to flat table format

    Yes very close, but I would only want to see the base entities and the parents to the left. So for example line 9 and 10 on your Excel file are perfect because they go all the way down to the base entity in column N. Row 8 stops at column M but I would want to eliminate this row all together because this row stops at "STC EASTERN OP-72331" in column M which is a parent of "STC EASTERN OP-06.72331" & "STC EASTERN OP-99.72331" which are the two rows below it. Each row on the spreadsheet should only go all the way down to a base entity. If the row stops at a parent entity...eliminate.

    So I would eliminate rows 2-8 because they stop at a parent entity. Rows 9 & 10 go to the base entity in that hierarchy, so keep... Eliminate 11 & 12, etc, etc.

    Hope this helps!

    Let me know if you have any additional questions.

    Thanks and is starting to look good!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Cross-table into flat list
    By Arnodekkers1995 in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-02-2016, 05:10 PM
  2. Macro to keep cell hierarchy in table format
    By ThibaultOlbrechts in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-09-2015, 07:55 AM
  3. Trying to convert to a flat table for pivot analysis
    By Vaslo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2013, 07:03 PM
  4. Convert indentured parent child table to flat file
    By Liberator in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-12-2013, 11:29 PM
  5. Formula or VBA to fill down a hierarchy to create a flat file
    By bmb163 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-28-2012, 09:34 AM
  6. Replies: 3
    Last Post: 11-02-2012, 06:45 PM
  7. Macros to change cross tab to flat list table
    By jtd84 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-05-2012, 06:12 PM

Tags for this Thread

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