Comment on Sorting and Filtering tutorial

Discuss the spreadsheet application
Post Reply
Iritscen
Posts: 4
Joined: Wed Nov 26, 2008 3:03 am

Comment on Sorting and Filtering tutorial

Post by Iritscen »

Many thanks for this tutorial, MrProgrammer; I needed a way to sort a list of numbers automatically, and couldn't put the pieces together mentally until I read your post. As you know and probably chose to omit for readability, the sorting can be performed in just one column instead of three.

Concise Sorting (contiguous)
In the sample sheet's sorting tab, one can replace the formula in D2 with:
=IF(ISBLANK(A2);"";SMALL(OFFSET($A$2:$A$21;0;0;COUNT(A$2:A$21);1);CELL("row";D2)-1))
Then one can copy the formula down column D to get the same sorted results, and do away with columns B and C. MrProgrammer's way of presenting the solution is obviously more readable. I just thought that not everyone would know the "using CELL() to provide incremental numbers" trick and might find it helpful when trying to save columns in their spreadsheet.

Non-Contiguous Data
However, some users may need to sort a column with some blank spaces between numbers (which was my situation). In this case, the whole formula unfortunately unravels, dropping numbers from the sort results and producing errors. The reason is that the use of the OFFSET() and ISBLANK() functions is based on the assumption that the data is contiguous.

One alternative approach is to change the counting formula in B2 to:
=ROW(INDEX(A1:A65535;MAX(IF(ISBLANK(A1:A65535);"";ROW(A1:A65535))))) (be sure to enter this as an array formula)
The 65535 is just an example of a max number. You might know that your data can only be 5000 rows long and use that number instead, or you might want to play it safe and go with OOo's maximum row number of 1048576 — but that takes a few seconds to calculate. Note that this is no longer a counting algorithm, which is useless for non-contiguous data, but rather provides the last used row in the range A1:A65535.

Then change the formula in D2 to:
=IF(ISERR(SMALL(INDIRECT("A2:A"&B$2);CELL("row";D2)-1));"";SMALL(INDIRECT("A2:A"&B$2);CELL("row";D2)-1))
Then copy that formula down the column. One could always replace B$2 with the formula in B2 to require no additional columns, but the formula is already quite long and B2 formula's is pretty CPU-intensive so you don't want to perform it multiple times. Unfortunately the use of IF(ISERR()) to screen out #VALUE! errors requires the SMALL() function to be called twice in each cell, unless someone knows a way to slim things down. It still runs pretty fast, as far as I can tell.

Ultimately, some people might prefer to just make a macro to do all this, but it's still doable with formulas, so that's what I'm sticking with for my own needs!

Edit: Corrected B2 counting formula, I forgot that I added ROW() to the beginning of it. Otherwise it returns the last value, not the last value's row.
Last edited by MrProgrammer on Thu Feb 23, 2023 8:17 pm, edited 1 time in total.
Reason: Added link to referenced tutorial
Post Reply