+ Reply to Thread
Results 1 to 20 of 20

How do I convert column hierarchy to flat file hierarchy

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

    Question How do I convert column hierarchy to flat file hierarchy

    I have a hierarchy of office data that is set up on one column (A) and need to get this into a flat file format table with multiple columns. Below is a view of what column A Looks like:

    Untitled.png

    Need to convert above list into table with multiple columns with column 1 being the parent, and columns to the right being the children....

    So to the right of Column A I will have a separate table with 8 columns. With parents/grandparents on the far left (Column B) and all the individual children to the right.

    Let me know if you have any questions.

    Thanks!
    Last edited by tbucki1; 08-22-2016 at 05:53 PM.

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

    Re: How do I convert column hierarchy to flat file hierarchy

    Post a workbook instead of a picture, and show before and after examples.
    Entia non sunt multiplicanda sine necessitate

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

    Question Re: How do I convert column hierarchy to flat file hierarchy

    Attached is an example of what I am looking to achieve... Left side of excel sheet shows current view... need to convert to flat file table view in table E3:G13.

    I did a quick mock up of US States...

    **Note hierarchy in column A is created using spaces before the actual text.**

    Let me know if you have any questions.

    Thanks!
    Attached Files Attached Files

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    This solution uses 3 helper columns by one formula in B2 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    Then in the table E4 array-entered filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If you arenít familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.
    Dave

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

    Re: How do I convert column hierarchy to flat file hierarchy

    Beautiful! That is perfect!

    Thanks so much!
    -Travis

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

    Question Re: How do I convert column hierarchy to flat file hierarchy

    Hi FlameRetired,

    I was working with the formula you provided and I think I got something wrong with the formula...

    Tried applying it to my spreadsheet but only part of it works... Attached is the actual document.

    Maybe something with the array or "zzzzzz"?

    -Thanks!
    Attached Files Attached Files

  7. #7
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    Yes.

    You'll need to change
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    to
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If the number of helper columns is variable consider a helper cell with this array-entered formula in say A3.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This will return the correct number in earlier formula by referencing it instead of a constant. i.e. IF(COLUMNS($B:B)<$A$3,..........


    Edit Although it is not a problem in the most recent upload you might want to consider replacing the array constant

    {4,8,12,16,20,24,28,32,36,40,44,48}

    with a range reference containing this array-entered formula in B3 filled across. It will calculate the leading space numbers. You can then reference them as $B$3:$I$3 or however many columns you extend this before you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    In that case the formula in A3 can be simplified to COUNT(B3:I3).

    I've included a file with this edit.
    Last edited by FlameRetired; 08-23-2016 at 11:18 PM.

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

    Re: How do I convert column hierarchy to flat file hierarchy

    That worked! Thanks so much!.... Just out of curiosity, what does the Lookup("zzzzzzzzzzzzz",) function do?

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

    Question Re: How do I convert column hierarchy to flat file hierarchy

    Hi FlameRetired,

    Sorry, after looking through the data I noticed that some columns are off by one row and some of the columns in the far right are pulling in data that isn't in that hierarchy. I tried to attach the document, but the file size is 2.5M which is too large to upload in .XLSX so I moved it to .ZIP. (See Attached)

    Notice in Column K row 503 Great Falls moves to the Spokane line... and in column L Great Falls still appears in the "Security-206002-99" rows 509 - 512 and on every row going down.... Seems like something in the formula needs to be adjusted but I am not sure where.

    Thanks so much for all your help! I have learned a lot about array's here.

    Best,
    Travis
    Attached Files Attached Files

  10. #10
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    Travis,

    Yes. I did not see that one coming. Grrr!

    With the same helper cell in A4 try replacing the old formula with this in B5 filled down and across.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    This works at my end. It also works much faster.

    And BTW. If you attempt to use the array formula (my first post) in a summary table you will want to reconsider. Array formulas are resource hungry. The latest upload will be very slow if you attempt it with that much data. Let me know what you plan to do re: the summary. I have a work around, but it requires two more helper columns.

    If you have difficulties I will attempt to upload a zip file. (I've never uploaded a zip before. What I see so far is not promising.)

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

    Re: How do I convert column hierarchy to flat file hierarchy

    Thanks so much! Yes I noticed the array was dragging down my excel.

    Let me know the quicker way around this with the extra helper cells and I will redo the summary...

    Best,
    Travis

  12. #12
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    In N5 of 'Level Mappings' this formula.
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    I just double-clicked the fill handle and it was done.

    Then in E2 of 'DeDup' this helper formula filled down until you get blanks.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    Then in F2 of 'DeDup' this summary formula filled down until you get blanks and across to column Q.
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    If I follow the sense of your first upload you only want to return completed rows ... ie those that contain no empty cells. I found only 14 rows that have that condition. Is this what you expected?

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

    Question Re: How do I convert column hierarchy to flat file hierarchy

    Think you might have lost me with one of the three helper equations...

    Attached is what the completed file will look like. I didn't use formulas since this was just a mock up... but this summary page should have formulas in it.

    Everything will be in one big table.

    Column A is the old vertical hierarchy and column B-M would be the flattened hierarchy version. I think we were close before...

    So when there are no more children it stops going right...

    Given all the formulas, how would you build this without Arrays so that excel is still fast and doesn't crash...

    Thanks so much!!
    Travis
    Attached Files Attached Files

  14. #14
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    None of the formulas I my last post are array formulas. The first two are helper columns. They assign row numbers to the qualifying rows instead of an array of assigned index numbers (as in array formulas). The first helper identifies the row #s.The second one consolidates (groups) those numbers in 'DeDup'. That step saves the final formula from having to re-sort those row numbers in each column/row of summary output. There are no arrays involved.

    The last formula outputs the summary of all rows with 'dependents' extending to column M 'Level Mappings'.

    In your most recent upload I did the same thing. In column O Sheet1 this assigns row#s
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    It only found only 17 qualifiers starting way down in cell O3769.

    Then in Sheet2 (I added that sheet) this groups / consolidates those numbers. This step is designed to 'lighten' the load in the final formula. It is in column A
    Formula: copy to clipboard
    Please Login or Register  to view this content.



    Then the final summary formula in B2:M18
    Formula: copy to clipboard
    Please Login or Register  to view this content.

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

    Re: How do I convert column hierarchy to flat file hierarchy

    Hi FlameRetired,

    I noticed in the latest file you posted, version 4, the "sheet 1" table contained pasted values. Were these suppose to be formulas? Trying to follow the logic on how you were able to take the hierarchy and flatten it into table format.

    Thanks!

  16. #16
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    I don't understand how those became pasted values. They are supposed to be formulas.

    Checking on it.

  17. #17
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    I don't know what happened. I don't recall doing that. I had every reason to not to.

    I am going to have to re-build the formula from scratch. Arrrghh !!

    Does the project require formulas applied to a table? I find the table syntax awkward. It has its own "rules". They aren't friendly.

  18. #18
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    I may have found it.

    I am attempting to upload a zip file.

    It appears to be what I was working with. In that "pasted" file I was focusing on the final page summary of the parsed data from the first sheet
    Attached Files Attached Files

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

    Re: How do I convert column hierarchy to flat file hierarchy

    That worked! Thanks so much.

    -Travis

  20. #20
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    Office 365, rarely 2007
    Posts
    12,663

    Re: How do I convert column hierarchy to flat file hierarchy

    You're welcome. Thanks for the feedback and for posting this one. I found it both interesting and challenging.

    Just glad I located my file. LOL

+ 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. Convert hierarchy list to flat table format
    By tbucki1 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2016, 04:19 PM
  2. Creating a Pyramid Hierarchy structure from a flat structure
    By thegamerulez in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-16-2014, 01:28 AM
  3. Create 5 column hierarchy off of one column extract
    By phil3061 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-08-2013, 10:26 AM
  4. 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
  5. Hierarchy Indented List, Table, Column and Value - need to create uniqueid column
    By mcolli01 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-06-2012, 01:09 PM
  6. Labeling Column A based on hierarchy/duplicates
    By Figgylynn1023 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 07-28-2011, 11:15 AM
  7. How to ascertain path of file in adjacent directory hierarchy
    By Paul Martin in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-09-2005, 01:05 AM

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