Ranking values (ignoring duplicates)

[cstimart]

I have a spreadsheet that has 20,000+ rows of data. Column B contains a date for each row. Is to possible to "rank" each of the dates so that any dates which are the same have the same rank?

For example, there are 20,000 rows of data and there is 78 different dates. I would want to rank (in column A) each line from 1 to 78. (Neither the 20,000 or the 78 are steadfast numbers)

 

[jimrward]

my first stab would be of the thought that the earliest date in the series would be ranked 1, next 2 etc

to find this I would use something like

=B1-MIN($B$1:$B1$20000) + 1

if you want the earliest date to be ranked 78 then change MIN in the above to MAX

 

[jimrward]

on second thoughts my thinking is flawed if there are gaps in the dates, then you will have a break in the ranking

I will see if I can come up with something else

ok a bit of googling has come up with the following

http://www.tushar-mehta.com/excel/newsgroups/ranking/

see the following section

Retain duplicate values but create continuous ranks, i.e., no breaks in the rank

 

[cstimart]

my first stab would be of the thought that the earliest date in the series would be ranked 1, next 2 etc

to find this I would use something like

=B1-MIN($B$1:$B1$20000) + 1

if you want the earliest date to be ranked 78 then change MIN in the above to MAX

I tried this (removing the "1" noted above), and the first data ranking was 1, and then the next was 15, rather than 2, as the dates were 14 days a part.

 

[narrowbarrow]

In my attempt to avoid large/nested/array formulas slowing down calculation, here's how I resolved this problem for myself: Make a separate "helper" pivot table (either out of sight on the same worksheet, a hidden tab, wherever) with dates in the Row Labels column as well as the values column, summarized by average (right-click the values column to do this). Right click the values column again and select "Show values as...Rank smallest to largest." This will give a sequential and contiguous list i.e. 1,2,3,4,5,6 regardless of the date repetition. Then if you want to have each date's rank feed back into your original data source, a simple vlookup referencing the pivot table's range will do the trick. Remember of course to refresh pivot tables after new dates are entered.