Ranking numbers AND text

  • Thread starter Thread starter Neil G
  • Start date Start date
N

Neil G

Dear group
Is it possible to Rank a list of numbers and text, such that the numbers
come out reverse and then the text last?

For example
1
3
2
F
EF
FX

Would get ranked to
3
2
1
EF
F
FX
The order of the text ranking isn't really important. The numbers is though.

Thanks
Neil
 
Is it possible to Rank a list of numbers and text, such that the numbers
come out reverse and then the text last?

For example
1
3
2
F
EF
FX

Would get ranked to
3
2
1
EF
F
FX
The order of the text ranking isn't really important. The numbers is though.
Click to expand...

Do you mean sorting? If so, not with just this one column. You seem to want
numbers sorted in descending order with text below in ascending order. You could
use a derived second column as the sort key. If the sample data above were in
A1:A6, then enter the formula

B1:
=IF(ISNUMBER(A1),COUNTIF($A$1:$A$6,"<"&A1)+COUNT($A$1:$A$6),
COUNTIF($A$1:$A$6,">"&A1))

and fill B1 down in B2:B6. Then sort A1:B6 on column B in descending order.
Finally, clear B1:B6.
 
Harlan

No, not really sorting, I don't want to have to sort them manually, but rank
them automatically.

Neil
 
Maybe this might help ..

Assuming your example list below
is in A1:A6 in Sheet1:

1
3
2
F
EF
FX

In Sheet1
---------
Put in B1: =IF(ISTEXT(A1),ROW()/100000,A1)
Copy down to B6

In a new Sheet2
---------------
Put in A1:

=OFFSET(Sheet1!$A$1,MATCH(LARGE(Sheet1!B:B,ROW()),Sheet1!
B:B,0)-1,0)

Copy A1 down to A6

This would give you the order you want,
i.e. numbers in descending order,
with all text thrown below
(text order not important)

3
2
1
FX
EF
F
 
Hi Neil

Here's one way:

Assuming data in B2:B20 (blanks allowed and numbers
and text can be mixed, e.g. number in B2:B3, text in B4:B6,
numbers in B7:B10 etc)

enter this array formula in E2:

=IF((ROW()-ROW($E$2)+1)<=COUNT($B$2:$B$20),LARGE($B$2:$B$20,
ROW()-ROW($E$2)+1),INDEX($B$2:$B$20, SMALL((IF(ISTEXT($B$2:$B$20),
ROW($B$2:$B$20)-ROW($B$2)+1)),ROW()-ROW($E$2)+1-COUNT($B$2:$B$20))))

the formula must be entered with <Shift><Ctrl><Enter>, also if edited
later. If done correctly, Excel will display the formula in the formula
bar enclosed in curly brackets { }. Don't enter these brackets yourself.

Copy E2 to E20 by dragging the fill handle (the little square in the lower
right corner of the cell).

E2:E20 will now start with all numbers in descending order after which
come the text cells in the original order. Empty cells in B2:B20 will be
displayed as #NUM! at the end of the list.
 
No, not really sorting, I don't want to have to sort them manually, but rank
them automatically. ...
Click to expand...
...

I suppose it wasn't obvious, but if the formula above could be used to sort the
range of your sample values (assumed in A1:A6), then the formula also gives the
ranking you're seeking. The specific formula above gives the ranking of cell A1
in A1:A6, where A1 contains 1, and the formula returns 3.
 
...
...
..

I suppose it wasn't obvious, but if the formula above could be used to sort the
range of your sample values (assumed in A1:A6), then the formula also gives the
ranking you're seeking. The specific formula above gives the ranking of cell A1
in A1:A6, where A1 contains 1, and the formula returns 3.
Click to expand...

Actually this formula copied to cell B2 gives 5 for the number 3 and copied to
B6 gives 0 for the text FX. If you want the rank 1 to correspond to the number
3, change the formula above to

B1:
=COUNT($A$1:$A$6)-IF(ISNUMBER(A1),COUNTIF($A$1:$A$6,"<"&A1)+COUNT($A$1:$A$6),
COUNTIF($A$1:$A$6,">"&A1))
 
Back
Top