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

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 2

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