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

bruty

Active Member
Joined
Jul 25, 2007
Messages
456
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
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.
 
Upvote 0
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.
 
Upvote 0
Find/Replace asterisks (Find what: ~* / Replace with: -10000)
Sort
Find/Replace (Find what: -10000 / Replace with: *)

Same could be done with VBA
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,234
Messages
6,170,891
Members
452,366
Latest member
TePunaBloke

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top