Sort order in different column as a numeric value

  • Thread starter Thread starter Gunjani
  • Start date Start date
G

Gunjani

In 'Column A' I have list of dates in in different order. In 'Column B'
I wish to place a numeric value (1st, 2nd, 3rd etc) to reprsent the date
order eg. A1=12/03/04, A2=16/12/04, A3=09/02/04, A4=10/10/03 hence in B1
=3,B2=4,B3=2 and B4=1 or better still...
B1=3rd,B2=4th,B3=2nd,B4=1st
--
Many Thanks

Gunjani
3 kinds of people: those who can count and those who
can't.
 
Gunjani,

In cell B1, use the formula

=RANK(A1,$A$1:$A$XX)
&IF(AND(RANK(A1,$A$1:$A$XX)>10,RANK(A1,$A$1:$A$XX)<20),"th",CHOOSE(MOD(RANK(
A1,$A$1:$A$XX),10) +1,"th","st","nd","rd","th","th","th","th","th","th"))

replace the XX with the row number of the last date you want ordered, then
copy down to match your dates. Watch the line wrapping on the formula.

HTH,
Bernie
MS Excel MVP
 
Gunjani,

Sort the table on column B. Do not, under any circumstances, select a
single column, unless you want to sort only that column. Select the entire
table, or if your table is together with nothing touching it, click a single
cell in column B and use the sort buttons on the Standard Toolbar.
 
Gunjani:

3 kinds of people. Those who can count and those who can't.

And those who think they can count.

Al
 
Hi
have a look at the RANK function for this
Click to expand...
Thanks... except how do I prevent #N/A displayed for the blank cells in
the range; replace it with blank space or - in the cell
--
Many Thanks

Gunjani
One who fears limits his activities. Failure is the only
opportunity to more intelligently begin again.
-- Henry Ford
 
Alf,

Actually, there are 10 types of people: Those who understand binary and
those who don't.

Bernie
MS Excel MVP
 
Back
Top