3

I'm trying to ensure a list of numbers is sorted in descending order. The array formula I have works great for lists without any blank cells, but it breaks when blanks are present. My data is in range R6:R825.

={AND('Tab 1'!R6:R824>='Tab 1'!R7:R825)}

Is it possible to have the formula perform the same descending sort check while ignoring the blank cells?

kschindl
  • 223
  • 2
  • 12
  • This answer shows how you can create a UDF in VBA to only look at the non-blank cells in an array, or alternatively, without using VBA, create a helper column that strips out the blanks (which you could then run your original formula against): http://stackoverflow.com/questions/20472388/how-to-find-all-non-blank-cells-in-an-array-of-cells – CactusCake Jun 13 '16 at 15:05
  • Thanks for the link. I knew I could've used a helper column, but I was hoping to avoid that step, if possible. Is there another solution which avoids the use of a helper column? – kschindl Jun 13 '16 at 15:14
  • There may be a way to nest the blank-stripping helper column formula into the arrays used in your original formula, I'll see if I can figure it out. – CactusCake Jun 13 '16 at 15:16
  • While you are waiting for someone to come up with formula-based solution [here are some UDFs for your consideration](http://stackoverflow.com/questions/37239107/sort-range-without-sorting-it-in-a-spreadsheet/37239386#37239386) which can easily achieve what you are asking for. Yet, these are UDFs and not native Excel formulas and as such will always perform slower and do not always update (unless you are using `Application.Volatile` and thus reduce the overall performance of your workbook). – Ralph Jun 13 '16 at 18:11
  • Welp, there's a bunch of different ways to do helper columns, but I can't figure out how to get them to work as an array inside an existing array formula, sorry. It seems like the array functionality only gets applied to the outermost layer or something. VBA might be your best option here. – CactusCake Jun 13 '16 at 19:53

1 Answers1

0

You could use this simple formula to sort the numbers in descending order,

=IFERROR(LARGE(A:A,ROW()),"")

Drag the fill handle or double click to sort the complete range. The above formula works only if the data starts from row 1. Looks like your data starts from row 6 (R6). Hence use the below formula,

=IFERROR(LARGE(R:R,ROW()-5),"")

Gowtham Shiva
  • 3,802
  • 2
  • 11
  • 27