CODEWITHSUNDEEP
excelexcel-formularanking
Helle Thorning
Helle Thorning

Reputation: 49

Excel ranking of duplicates

I am ranking some repetitive data with =IFERROR(COUNTIF($I:$I, ">" &I2) + 1, 0) and RANK functions but I get

sum         rank
1.948730668 1
1.948730668 1
1.948730668 1
1.948730668 1
1.948730668 1
1.948730668 1
1.948730668 1
1.948730668 1
1.948730668 1
1.944370401 10
1.944370401 10
1.944370401 10
1.940616775 19
1.940616775 19
1.940616775 19
1.940616775 19
1.940616775 19
1.940616775 19
1.940616775 19
1.932373487 26
1.932373487 26
1.932373487 26
1.932373487 26
1.932373487 26
1.932373487 26

I am looking for the ranking output to be 1, 2 in place of 10, 3 in place of 19, 4 in place of 24, and so on.

Do you know to get the wanted output?

EDIT: After seeing a couple of comments, I just want to say that data is not fully sorted. This is another example:

0.746083463 4401
1.585145695 448
0.917176157 3325
0.640143457 5222
1.061271163 2376
1.252380073 1351
1.43915555  804
1.800414518 134
1.500507287 626
0.96121049  3012

Upvotes: 1

Views: 158

Answers (2)

Slai
Slai

Reputation: 22876

Here is one alternative in B2 and copy down:

=IFERROR(INDEX(B$1:B1, MATCH(A2, A$1:A1, 0)), MAX(B$1:B1) + 1)

or a bit shorter (and maybe a bit slower) with VLOOKUP:

=IFERROR(VLOOKUP(A2,A$1:B1,2,0),MAX(B$1:B1)+1)

Upvotes: 0

ttaaoossuu
ttaaoossuu

Reputation: 7884

Here's the trick (assuming your values are in column A:A):

In cell B2:

=1

In cell B3 and below:

=$B2+IF($A3<>$A2,1,0)

Upvotes: 2

Related Questions