+ Reply to Thread
Results 1 to 7 of 7

Sorting of rows belonging to one of four hierarchical levels

  1. #1
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Sorting of rows belonging to one of four hierarchical levels

    Hi,

    I have exported data from a maintenance database, where each row belongs to one of four levels in a hierarchy. Imagine a machine being the top level, and then you drill down through the hierarchy to main parts, their subparts until you reach the smallest bits.

    Each row has a unique asset number for the specific (sub-)part, accompanied by a "parent asset" to which it belongs. The parent asset is the immediate level above, if any.

    I have attached a workbook with an example, where I have added an extra column A, where I have manually indicated which level each row represents.

    Only one row has the top level 1, while in this example there are two rows that have the next level 2, etc. The parent asset cell is blank for the top level 1, since it hasn't got any parent level above it.

    My problem is that the exported rows don't come sorted in a meaningful way, and they can't be sorted the way I want by using the regular sort function in Excel.

    I am therefore looking for a way to create helper columns that I can use for sorting purposes.

    I want to sort the rows this way:

    Level 1 followed by the alphabetically next level 2 row, followed by this level 2 row's alphabetically next level 3 row, followed by this level 3 row's alphabetically next level 4 row(s) - insofar that each level contain rows to sort.


    Level 1
    Level 2.1 belonging to level 1
    Level 3.1 belonging to level 2.1
    Level 4.1 belonging to level 3.1
    Level 4.2 belonging to level 3.1
    Level 4.3 belonging to level 3.1
    Level 3.2 belonging to level 2.1
    Level 4.1 belonging to level 3.2
    Level 2.2 belonging to level 1
    Level 3.1 belonging to level 2.2
    Etc.

    Does anybody have a solution for this?

    Regards,
    Marbleking
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    07-20-2011
    Location
    Mysore, India.
    MS-Off Ver
    Excel 2019
    Posts
    8,513

    Re: Sorting of rows belonging to one of four hierarchical levels

    It is not clear. Taking few rows(approx 20) give the expected result in the file explaining how it is required .
    Pl note
    Array formula should be confirmed with Ctrl+Shift+Enter keys together.
    If answere is satisfactory press * to add reputation.

  3. #3
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Sorting of rows belonging to one of four hierarchical levels

    Hi,

    I have added a tab with a smaller sample that illustrates how I want the data to be sorted.

    Regards,
    Marbleking
    Attached Files Attached Files

  4. #4
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Sorting of rows belonging to one of four hierarchical levels

    Hi,

    Attached is an updated workbook, where I have included a solution that I found. The correct sorting of columns S (parent level) and T (child level) ensues in table "Solution" when the columns M to Q (1st ... 5th generation) are sorted in ascending order.

    If anyone has a more elegant solution, I'd be happy to see it.

    Regards,
    Marbleking
    Attached Files Attached Files
    Last edited by Marbleking; 10-18-2022 at 09:27 AM.

  5. #5
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Sorting of rows belonging to one of four hierarchical levels

    I'm not clear, did 2nd column is unique ?
    I've found some duplicate of 2nd column in 'Sorting' sheet.

    Could you please show your 'fake but similar to real data' and desired output?

    Regards.

  6. #6
    Forum Expert
    Join Date
    09-30-2019
    Location
    Chiangmai, Thailand
    MS-Off Ver
    Office 2016, Excel 2019
    Posts
    1,234

    Re: Sorting of rows belonging to one of four hierarchical levels

    From your last sample file (2nd column is unique).

    Create temp table

    D4
    =IFERROR(INDEX($A:$A,MATCH(E4,$B:$B,0)),"")

    E4
    =IFERROR(INDEX($A:$A,MATCH(F4,$B:$B,0)),"")

    F4
    =IFERROR(INDEX($A:$A,MATCH(G4,$B:$B,0)),"")

    G4
    =IFERROR(INDEX($A:$A,MATCH(H4,$B:$B,0)),"")

    H4
    =B4

    Generate key for sort.
    J4
    =Concat(D4:H4)

    Do sort (for me it's by sort command / for O365 can use SORT function)

    Row
    N4
    =MATCH(L4,$J$4:$J$24,0)+ROW($N$3)

    Output
    O4
    =INDEX(A:A,$N4)

    P4
    =INDEX(B:B,$N4)

    Regards.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    08-20-2011
    Location
    Oslo
    MS-Off Ver
    MS 365
    Posts
    245

    Re: Sorting of rows belonging to one of four hierarchical levels

    Hi menem! Thanks a lot! Very nice solution, thanks!

+ 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. Sorting an multilevel / hierarchical column
    By Saintor in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 01-07-2022, 09:35 AM
  2. Dissolve hierarchical structure into multiple rows
    By snelstim in forum Excel General
    Replies: 0
    Last Post: 05-17-2021, 05:31 AM
  3. [SOLVED] Sorting will not hold the the information belonging to the row.
    By The Force in forum Excel General
    Replies: 19
    Last Post: 08-31-2020, 10:41 PM
  4. Replies: 3
    Last Post: 11-06-2019, 09:26 AM
  5. Hierarchical Sorting Problem
    By oglesbyr in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 11-19-2015, 10:57 AM
  6. How do I group rows hierarchical (1, 1.1, 1.1.2 ...)
    By jugeh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-09-2013, 05:19 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