Sort order in different column as a numeric value

[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.

 

[Frank Kabel]

Hi
have a look at the RANK function for this

 

[Bernie Deitrick]

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

 

[Earl Kiosterud]

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.

 

[AlfD]

Gunjani:

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

And those who think they can count.

Al

 

[Gunjani]

Hi
have a look at the RANK function for this

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

 

[Bernie Deitrick]

Alf,

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

Bernie
MS Excel MVP

 

[AlfD]

Bernie

Touche (how the heck do I do an accent here?)

Al