Rank duplicates by another column

[felix11]

Hi,

Is it possible to give ranks to duplicate values based on the values in another column? Each set of duplicate values should have its own rank set. I can do it for a small dataset using a combination of IF, INDEX and SMALL and then using the RANK function on what this returns, but I could not automate this for a large dataset.

An example is
Object Year
A 2001
B 2004
C 1987
C 1999
A 1955
C 2006
A 2009

I want to return the rank of A compared to the other A's based on their year, i.e.
Rank
2
1
3
2
3
1
1

Any help would be much appreciated, I have been trying for a while now.

[NBVC]

Assuming data is in A1:B7, try:

=SUM(IF(IF($A$1:$A$7=A1,$B$1:$B$7,0)>B1,1))+1

confirmed with CTRL+SHIFT+ENTER and copied down

[felix11]

Thank you very much! Works fine.

I often completely overlook/forget array functions in cases they're perfectly suited to.