The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

    Mastodon

  • Twitter Updates

  • My Flickr Stream

    20140508-Delphi-2007--Project-Options--Cannot-Edit-Application-Title-HelpFile-Icon-Theming20140430-Fiddler-Filter-Actions-Button-Run-Filterset-now20140424-VMware-Fusion-6.0.3.-no-reclaimable
    More Photos

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,860 other subscribers
«
»

Excel: sorting an array on 3 different columns using functions

Posted by jpluimers on 2025/09/09

Given Excel is the most popular functional language (see the video under my post “Everybody should have an obsession with Lisp-like language at least once in their life” @KevlinHenney), this post is on sorting with Excel functions.

I had an array in Excel starting at row 2 (to exclude the headings) where I had to sort on 3 different columns: C, A, B (or numerically 3, 1, 2) so [Wayback/Archive] sorting – How to multi level sort an array in excel, using formulas? I am aware about the way, using SORT button on DATA tab – Stack Overflow (thanks [Wayback/Archive] Armaan Gohil and [Wayback/Archive] Jos Woolley) didn’t fully apply but did put me on the right track as I wasn’t aware that the SORT function allows to specify multiple columns using an embedded array argument.

The functions that worked:

  • =SORTBY(A2:.D1048576,C2:.C1048576,1,A2:.A1048576,1,B2:.B1048576,1)
  • =SORT(A2#,{3,2,1})
  • =SORT(A2:.D1048576,{3,1,2})

Notes:

  • 1048576 is the maximum number of rows in Excel 2007 and up; for Excel 2003 and lower you would use 65536
  • the :. is a trick to skip empty values at the end of the array, see the below TRIMRANGE function reference
  • the # is a trick named spilled range operation to skip empty cells at the end of an array as well
  • {3,1,2} is 1 row by 3 column array constant with values 3,1,2; ordered arrays like {1,2,3} and {3,2,1} could be replaced by SEQUENCE(,3) and =SEQUENCE(,3,3,-1) which can be easier localising with varieties in settings for separators ; (US-English: row separator) and , (US-English: column separator)
  • [Wayback/Archive] SORT function – Microsoft Support does not indicate the second parameter can be an array:

=SORT(array,[sort_index],[sort_order],[by_col])

Argument Description
array
Required		 The range, or array to sort
[sort_index]
Optional		 A number indicating the row or column to sort by
[sort_order]
Optional		 A number indicating the desired sort order; 1 for ascending order (default), -1 for descending order
[by_col]
Optional		 A logical value indicating the desired sort direction; FALSE to sort by row (default), TRUE to sort by column

In the end, I wanted to convert the array to a list and trim it down to unique and non-empty values. A query for the former suggested to use ARRAYTOTEXT, but that results in text, not a list. You can kind of combine it with TEXTSPLIT if the ARRAY values themselves do not contain the split delimiter.

FILTER seems an OK option to filter empty cells, though “empty” has various manifestations in Excel: empty string "", zero integer 0, no value at all, error, and others.

Be aware of the #VALUE! result when using FILTER: though that whereas the filter range is a 2-dimensional array, the filter criteria are 1-dimensional arrays that need to be either as wide or as tall as the filter range (see one of the links below)

Also in that link: for array functions, there is a little &"" trick that converts any empty array cell into a cell having an empty string "".

Without including owners, I could have chosen for a jagged or sparse array/table containing the instruments per opus, then go from there using for instance HSTACK/STACK/FILTER.

In the end, I used these functions:

  1. =FILTER('Percussie-per-werk'!D2:.F1048576,('Percussie-per-werk'!C2:.C1048576<>"niemand")*('Percussie-per-werk'!C2:.C1048576<>"")) which gets me the percussion instruments that somebody (note the * trick: not "niemand" *and* not "") plays as we have limited percussionists
  2. =UNIQUE(A2:.C1048576) gets the unique list
  3. =SORT(E2#,{3,2,1}) sorts the list on columns 3 (is this on the program), 2 (owner), 1 (instrument)

and

  1. =FILTER('Percussie-per-werk'!A2:.D1048576,('Percussie-per-werk'!C2:.C1048576<>"niemand")*('Percussie-per-werk'!C2:.C1048576<>"")) gets the opus (column A), part layout(column B), who (column C) and instrument (column D)
  2. =UNIQUE(A2#) just in case there are any duplicates
  3. =SORT(F2#,{3,4,1}) shows who (column 4) has what (column 3) to play in which opus (column 1)
  4. =UNIQUE(A2:.A1048576) has the set list

Various instruments are owned by the club, various by individuals, and there are overlaps, hence getting a complete list is important.

Translations of worksheet and other names
Dutch English
Percussie-per-werk Percussion-by-opus
Instrumenten-en-eigenaren Instruments-and-owners
Wiens Whose
Niemand Nobody
Partij Part layout

In the future, I want to experiment with the LET function as pointed to in [Wayback/Archive] All values of an array to one column or row | Microsoft Community Hub (thanks [Wayback/Archive] SergeiBaklan)

If don’t touch functions which are only in Beta now, mock-up could be
=LET(
  data, $B2:Q350,
  rs,   ROWS(data),
  cls,  COLUMNS(data),
  k,    SEQUENCE(rs*cls),
  v,    INDEX(data, MOD(k-1,rs)+1, INT((k-1)/rs)+1 ),
  UNIQUE( FILTER(v, v<>"") ) )

Similarly, in the figure I might use CHOOSE, CHOOSECOLS (if my data was row-based, that would be CHOOSEROWS) to select sets of data to combine into an array for further processing.

References:

Posts/questions that inspired me:

Queries:

--jeroen

This entry was posted on 2025/09/09 at 18:00 and is filed under Development, Excel, Functional Programming, Office, Power User, Reddit, SocialMedia, Software Development. Tagged: . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.

«
»