CODEWITHSUNDEEP
excelsortingexcel-formula
TechGeek
TechGeek

Reputation: 2212

Excel - Ignore blanks in descending sort

In the attached file, when I sort on column C on descending order, it puts blanks on top. (it shouldn't happen)

Column D is just "Copy as values" of column C and sort works fine in column D

So I guess there is some issue with my formula in column C

File Link: https://drive.google.com/open?id=1TRDympt3-CFn6916aLGxd3du5SL_FaxM

Formula I am using: =IF(A3="","",IF(ISERROR(MATCH(TRIM(S!$A3),$G$4:$G$8,0)),$B3,""))

Upvotes: 2

Views: 17360

Answers (2)

Michael
Michael

Reputation: 4838

If you need to be able to sort in either ascending or descending order, then I can't think of a simple solution just through formulas.

The next simplest solution may be just applying an autofilter to exclude blanks (which actually does get rid of nullstrings).

If you're automating this through VBA, I think it would be simplest to first sort ascending, apply a filter to remove nullstrings, then sort descending.

You could also potentially incorporate the top 10 filter.

Upvotes: 3

Michael
Michael

Reputation: 4838

This is caused by the difference between a null-string and an empty cell.

The result of your formula is the null-string "", which is not the same as the "nothing" in an empty cell.

Truly empty cells are ignored by Excel when sorting, whereas the null-string is actually a string containing just the null-string character and is included in sorting.

I suspect the reason column D sorted correctly for you is because you only copied the cells with normal values from column C. If you copy the entire column (including what look like blank cells), then the issue persists because then you've also copied the null-string characters.

In a column of values, as in column D, you can remove null-strings using the Text-to-Columns tool with the delimiter option and deselecting all delimiters. (Same as how you can convert numbers stored as text to actual numbers.)

However with formulas, as in column C, there is no way of telling Excel to return truly "Nothing".

As a workaround, if you only ever have positive values, you can change your formula to return the value 0 instead of the null-string "". As a result, the 0 values will be put at the bottom when sorting descending.

You can then also hide the 0 values by changing the number formatting of the column:

  1. Select the entire column in the data table
  2. Format Cells...
  3. On the Number tab, select "Custom" in the Category
  4. Manually change the Type value from "0.00" to "0.00;-0.00;"

This format says to display:

  • Positive numbers with 2 decimal places
  • Negative numbers with 2 decimal places and negative sign at the front
  • Zeroes as blank

Upvotes: 2

Related Questions