sort, blanks to bottom, even when resulting from a formula

[Ken Cowen]

When I sort my data by a column containing blank cells, the blanks go to the bottom regardless of whether I sort ascending or descending. I like that. When I have a column with a formula and the formula returns a blanks ("") in some cases, I would like those blanks to always be on the bottom. I have tried "" and " " but both sort to the top, unlike when the cells are just blank. Is there a way to control this behavior?

Thanks

Ken

 

[Joe4]

I cannot reproduce that behavior. I just tested with empty cells, and blanks being returned by formulas, and both sort to the bottom for me.
Can you post your formula?
And how many columns are you sorting by? (is it just one of multiple? is this columns the FIRST column in the column sort order?)

 

[Ken Cowen]

This is the real formula

=IF(I3="Original","Original",IF(I3<>"","RCC "&TEXT(I3,"000")&L3,""))

I sort by only one column at a time, the data that is selected does not include a header row.

This is my test formula

=""

it gives the exact same result as the longer actual formula.

I want the blanks or those looking like blanks to stay on the bottom.

In actual use the sorting is typically triggered by double-clicking on a header row (not included in the sort range) to trigger the sort. It alternates between ascending and descending using the code below


Sub sortem(R, k)


'sort range R by column k, alternate ascending and descending order


If ord = False Then
R.Sort Key1:=Range(k), Order1:=xlAscending, Header:=xlNo
Else
R.Sort Key1:=Range(k), Order1:=xlDescending, Header:=xlNo
End If

ord = Not ord

End Sub

When I have blanks, which I do in several columns, they stay on the bottom, where they belong. A couple columns have formulas that often return blanks (like the sample above) and they alternate between being on the top and on the bottom.

Thanks

Ken

 

[Joe4]

OK. I see what you mean now.
However, it appears that if you sort in Descending order, it will then list all the blanks and empty cells at the end, like you want.

 

[Ken Cowen]

Joe
I was really hoping to keep them at the bottom regardless of sort order, as if they were blank cells. I think what is missing is the ability to make the cells NULL. Apparently a NULL worksheet function is on microsoft's list of things to add; I guess I will just have to wait.
Thanks for your time and effort.
Ken

 

[Joe4]

You may want to check out the various options here: https://www.extendoffice.com/documents/excel/1560-excel-sort-blank-cells-first.html

 

[perfectlyGoodInk]

The way I sorted blanks to bottom was to create a "cleaned" column that had the blanks replaced with zeroes. I used an if() with an isnumber() to replace them. If the column to sort with blanks is A and the "cleaned" columns is B, the cell B2 would have:

=IF(ISNUMBER(A2), A2, 0)

I then sorted the whole table by the cleaned column. Could easily do something similar with a negative value or even the minimum value of that column (just store that in a cell and make a fixed reference to that cell in the third argument to the IF() call.