Sorting Column that has formula

[bisel]

Greetings,

I have not been able to find an answer using search techniques and hoping some one may have a solution for me ...

I have a worksheet with a couple of columns that have formulas. The formulas' result is to conclude with either a specific cell's value (it will be numeric) or results in a null value ... i.e., cell value = "".

When I try to sort on these columns, if I select A to Z (i.e., low to high), it works fine. When I select Z to A (i.e., high to low), the numeric values are at the bottom and the null values are at the top. Which is not what I would like to see.

In my testing, if I convert all the formulas in the columns to values, the sort works as I would like.

Is there a way to resolve this behavior of Excel so that when I perform the sort function, it sorts low to high or high to low?

Thanks,

Steve

 

[arthurbr]

The "" text string has a TYPE() value of 2 ( which XL uses for comparisons) Therefore "" is before Z when sorting high to low
Any chance you can replace the "" with 0? ( and hide it with CF or XL options)<style type="text/css">p { margin-bottom: 0.25cm; line-height: 120%; }</style>

 

[bisel]

The "" text string has a TYPE() value of 2 ( which XL uses for comparisons) Therefore "" is before Z when sorting high to low
Any chance you can replace the "" with 0? ( and hide it with CF or XL options)<style type="text/css">p { margin-bottom: 0.25cm; line-height: 120%; }</style>

That thought occurred to me, however, the formula could also result in zero. Also thought of using negative numbers, but that would mess up the sort order.

 

[arthurbr]

You could use a helper column (say col B) containing =ISNUMBER(A1) pulled down
Which will return TRUE or FALSE then select A and B then sort by col B - Descending - then by col A - descending.
Cells with "" will then be after the numeric values. You can hide the helper column one way or another

 

[bisel]

You could use a helper column (say col B) containing =ISNUMBER(A1) pulled down
Which will return TRUE or FALSE then select A and B then sort by col B - Descending - then by col A - descending.
Cells with "" will then be after the numeric values. You can hide the helper column one way or another

Thanks for that suggestion. That is what I have done and then sorting on that column as the first key and then the primary column that I wanted sorted is working as I would like. The "helper" column is column R and I keep that hidden from view.

Sub sort_rulup()


Application.ScreenUpdating = False
On Error Resume Next
    
    activesheet.Sort.SortFields.Clear
    activesheet.Sort.SortFields.Add Key:=Range("R9:R258"), Order:=xlDescending
    activesheet.Sort.SortFields.Add Key:=Range("N9:N258"), Order:=xlDescending
    
    With activesheet.Sort
        .SetRange Range("rescomp_table")
        .Header = xlYes
        .Apply
    End With
    
    Range("N9").Select
    
End Sub

 

[shg]

The earlier Range.Sort method is a little more compact:

Sub sort_rulup()
  Range("rescomp_table").Sort Key1:=Range("R9"), Order1:=xlDescending, _
                              Key2:=Range("N9"), Order2:=xlDescending, _
                              Header:=xlYes
End Sub