Multi-level sorting in Excel with ignoring empty cells

Question

Multi-level sorting would probably be suitable for this task if there was an option “ignore empty cells”.

I need to sort the rows so that the sorting for column A ignores empty cells, and the sorting rule for column C is applied to rows with these empty cells.

But multilevel sorting simply moves rows with empty cells in column A to the end of the list, and only then does the sorting rule for column C apply.

Here's an example of what I mean. Here's an unsorted list of random music bands:

Band 1	Info 1	Band 2	Info 2.
The Beatles.	some info...		some info...
Queen	some info...		some info...
	some info...	Radiohead	some info...
	some info...	Nirvana	some info...
Coldplay	some info...		some info...
Metallica	some info...		some info...
U2	some info...		some info...
	some info...	Pink Floyd	some info...
Red Hot Chili Peppers	some info...		some info...
	some info...	Imagine Dragons	some info...

If I sort it using a multi-level sort with the first sorting rule on column A and the second sorting rule on column C, we have this result:

Band 1	Info 1	Band 2	Info 2.
Coldplay	some info...		some info...
Metallica	some info...		some info...
Queen	some info...		some info...
Red Hot Chili Peppers	some info...		some info...
The Beatles.	some info...		some info...
U2	some info...		some info...
	some info...	Imagine Dragons	some info...
	some info...	Nirvana	some info...
	some info...	Pink Floyd	some info...
	some info...	Radiohead	some info...

But the result I need looks like this:

Band 1	Info 1	Band 2	Info 2.
Coldplay	some info...		some info...
	some info...	Imagine Dragons	some info...
Metallica	some info...		some info...
	some info...	Nirvana	some info...
	some info...	Pink Floyd	some info...
Queen	some info...		some info...
	some info...	Radiohead	some info...
Red Hot Chili Peppers	some info...		some info...
The Beatles.	some info...		some info...
U2	some info...		some info...

The real table is much more complex, and sorting methods with auxiliary columns are highly undesirable.

I'm sure there is an easy way to do this sorting. But, it seems, I scoured the entire Internet. Perhaps I don’t know what this sorting is called correctly, and that’s why I couldn’t find

Answer 1

Try this array formula change the range to the actual size everywhere. You can create it simpler with the LET function.

=IF(CHOOSEROWS(A2:D11,CHOOSECOLS(SORT(HSTACK(CHOOSECOLS(A2:D11,1)&CHOOSECOLS(A2:D11,3),ROW(A2:D11)-1,),1,1,FALSE),2))=0,"",CHOOSEROWS(A2:D11,CHOOSECOLS(SORT(HSTACK(CHOOSECOLS(A2:D11,1)&CHOOSECOLS(A2:D11,3),ROW(A2:D11)-1,),1,1,FALSE),2)))

Result

Answer 2

Multi-Level Sorting

Initial Post and 1st Edit

2nd (Relevant) Edit

Inputs

• data - the range (array) reference - self-explanatory
• levels (default is 2) - in the screenshot, the data has 3 levels and each level has 2 columns. The levels can have any number of columns but their number has to be the same. The columns are calculated in the formula based on the total number of columns in the range.
• sort_col (default is 1) - is the column to be sorted by. In the 1st screenshot, the data is sorted by the first columns (BoysN) and in the 2nd by the 2nd columns (GirlsN).
• sort_order (default is 1) - can be ascending (1) or descending (-1).

Standalone

• You can use it as is or create a Lambda out of it. In the initial post is a fairly good description of how to do it.

=LET(data,B1:G21,levels,3,sort_col,1,sort_order,1,
    GetLeftMostEntry,LAMBDA(data,
        INDEX(data,SEQUENCE(ROWS(data)),BYROW(data,LAMBDA(r,
            IFNA(XMATCH(TRUE,r<>""),1))))),
    SortByCol,LAMBDA(headers,data,sort_array,[sort_order],LET(
        so,IF(ISOMITTED(sort_order),1,sort_order),
        VSTACK(headers,DROP(SORT(HSTACK(
           sort_array,data),,so),,1)))),
    dd,IF(data="","",data),
    d,DROP(dd,1),
    f,CHOOSECOLS(d,SEQUENCE(levels,,,COLUMNS(d)/levels)+sort_col-1),
    SortByCol(TAKE(dd,1),d,
        GetLeftMostEntry(f),sort_order))

Lambdas

• You can use the following 3 Lambdas to create your own functions in the Name Manager and utilize them with e.g.

  =MLSort(B1:G21,3,2,-1)
  

  for girls descending.

GetLeftMostEntry

• In each row of an array, returns the left-most value that is not nothing (<>"") or not blank.

=LAMBDA(data,
    INDEX(data,SEQUENCE(ROWS(data)),BYROW(data,LAMBDA(r,
        IFNA(XMATCH(TRUE,r<>""),1)))))

SortByCol

• Stacks the sort array to the left of the data, sorts the data by it, drops it, and stacks the headers on top. It's kind of optimized for the MLSort function since the headers and the data are passed separately.

=LAMBDA(headers,data,sort_array,[sort_order],LET(
    so,IF(ISOMITTED(sort_order),1,sort_order),
    VSTACK(headers,DROP(SORT(HSTACK(
        sort_array,data),,so),,1))))

MLSort

• Uses the previous two helper Lambdas to perform a "multi-level sort" coined by OP.

=LAMBDA(data,[levels],[sort_col],[sort_order],LET(
    lv,IF(ISOMITTED(levels),2,levels),
    sc,IF(ISOMITTED(sort_col),1,sort_col),
    so,IF(ISOMITTED(sort_order),1,sort_order),
    dd,IF(data="","",data),
    d,DROP(dd,1),
    f,CHOOSECOLS(d,SEQUENCE(lv,,,COLUMNS(d)/lv)+sc-1),
    SortByCol(TAKE(dd,1),d,GetLeftMostEntry(f),so)))

Three Levels - Boys ASC sort_col=1

Three Levels - Girls ASC (sort_col=2)

Answer 3

This can also be accomplished using Power Query, available in Windows Excel 2010+ and Microsoft 365 (Windows or Mac)

To use Power Query

• Select some cell in your Data Table
• Data => Get&Transform => from Table/Range
• When the PQ Editor opens: Home => Advanced Editor
• Make note of the Table Name in Line 2
• Paste the M Code below in place of what you see
• Change the Table name in line 2 back to what was generated originally.
• Read the comments and explore the Applied Steps to understand the algorithm

Edit:
This code is been edited to adapt to any number of Band/Info pairs

let

//Change next line to reflect your actual data source
    Source = Excel.CurrentWorkbook(){[Name="Bands"]}[Content],
    colNames = Table.ColumnNames(Source),
    #"Changed Type" = Table.TransformColumnTypes(Source,
        List.Transform(colNames, each {_, type text})),

    bands = List.Alternate(colNames,1,1,1),
    infos = List.Alternate(colNames,1,1,0),

//Unpivot the "Band" Columns
//Then sort by Band Name
    #"Unpivoted Columns" = Table.Unpivot(#"Changed Type", bands, "Attribute", "Value"),
    #"Sorted Rows" = Table.Sort(#"Unpivoted Columns",
        (x,y)=> Comparer.OrdinalIgnoreCase(x[Value], y[Value])),

//Create the different columns for the different bands
    colTypes= 
        Type.ForRecord(
            Record.FromList(
                List.Transform(
                    List.Repeat({type text},List.Count(colNames)), 
                        (t) => [Type = t, Optional = false]), colNames), 
            false),

    #"Added Custom"=Table.AddColumn(#"Sorted Rows","Custom", (r)=> 
        Table.FromRecords(
            {Record.Combine(
                {Record.FromList({r[Value]}, {r[Attribute]}) &
                 Record.SelectFields(r,infos) })}), 
        type table colTypes),

//Cleanup        
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom",List.RemoveItems(Table.ColumnNames(#"Added Custom"),{"Custom"})),
    #"Expanded Custom" = Table.ExpandTableColumn(#"Removed Columns1", "Custom", colNames)
in
    #"Expanded Custom"

Answer 4

Thanks everyone for the great answers!

Unfortunately, there were a lot of difficulties on my part, since I am very new to such difficult tasks. At first it turned out that Excel does not understand most of the commands (for example SORT) because it is licensed as Office 2016. But even after correcting the license to 2021 and reinstalling the entire Office package, problems with some other commands (such as CHOOSECOLS) remained. I never found out the reason for this. I had to completely reinstall Windows and the entire Office suite. And only after that I could start trying to check the answers.

However, for the case of my real table, they turned out to be too difficult for me. In my real table, there were three pairs of columns to which this two-level sort needed to be applied in turn. And in some of them, the name of the Band was written in both Band 1 and Band 2 columns (the same). And I realized that I didn’t have enough skill to rewrite these great examples from the answers to suit my case.

In the end, I simply resigned myself to three extra auxiliary columns, in which I simply wrote =IF(A2="";B2;A2), and sorted them using regular multi-level sorting. I came to this solution thanks to the answer from VBasic2008, which can be viewed at the link to the revision

I really appreciate everyone's really great answers and hope they help a lot more people. I really haven't found a single article describing such a task.