Sort a range of numbers (including an asterisk) with an asterisk as the lowest

[bruty]

I have two columns of data. The first is a count of respondents. The second is the average score for those respondents.

I need to sort the second column into numerical value, highest to lowest. So far, so simple.

The area I am having an issue with is that if the number of respondents is less than 21, the score is replaced with an asterisk. When I sort the columns now, the asterisk is placed at the top, but I want it to be shown at the bottom. Is there any way to do this?

I will eventually be performing this sort in VBA, if that makes it any easier?

 

[steve the fish]

Only way i can think of is to either change the asterisk to a zero or if you cant use a helper column where you do as such and sort by that.

 

[bruty]

Only way i can think of is to either change the asterisk to a zero or if you cant use a helper column where you do as such and sort by that.

Thanks. The helper column is the way I was thinking too, but was just wondering if I'd be able to do it without adding more data. Changing the asterisk is not an option as other things look at that further on in the spreadsheet.

 

[footoo]

Find/Replace asterisks (Find what: ~* / Replace with: -10000)
Sort
Find/Replace (Find what: -10000 / Replace with: *)

Same could be done with VBA

 

[bruty]

Find/Replace asterisks (Find what: ~* / Replace with: -10000)
Sort
Find/Replace (Find what: -10000 / Replace with: *)

Same could be done with VBA

Thanks for the suggestion

 

[footoo]

Changing the asterisk is not an option as other things look at that further on in the spreadsheet.

How are the asterisks used?
Also, what is the formula that returns the asterisks?
It is possible with cell formatting to display an asterisk when the cell contains a value <= 0, which means that the sort would be done in the way you want. But whether or not this is an option depends upon how the asterisk cells are being used.