# Rank Duplicate values

#### Clare1805

##### New Member
I have a list of values (some are duplicates) which I need to rank.

The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.

Example:
Value Rank Desired Rank
120 1 1
125 2 2
130 3 3
130 3 3
142 5 4

Any ideas?

### Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN

#### fairwinds

##### MrExcel MVP
Hi,

Try:

=RANK(A2,\$A\$2:\$A\$9,1)+COUNTIF(\$A\$2:A2,A2)-1

in B2 dragged down.

#### Clare1805

##### New Member
Thanks for the reply but this solution means that no two are ranked the same - I need the tied results to be the same number but the following number needs to be sequential. So, as per my previous example the rank of 3 would appear twice but would be followed by 4 (and not 5).

Is there a way to do this?

#### fairwinds

##### MrExcel MVP
Is the list sorted as in your example or can it be in any order?

#### Clare1805

##### New Member
It would be sorted in ascending order.

#### theozz

##### Active Member
<table style="border-collapse: collapse;" colspan="2" border="7" cellspacing="0"> <tbody bgcolor="#ffffff"> <tr height="36"> <td colspan="2"> DataSheet= SampleSheet</td></tr> <tr> <td align="right" width="30"> <table rowspan="12" cellspacing="1" width="30"> <tbody bgcolor="#ffffff"> <tr height="18"> <td>
</td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">1 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">2 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">3 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">4 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">5 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">6 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">7 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">8 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">9 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">10 </td></tr> <tr height="18"> <td align="right" bgcolor="#dcdcdc">11 </td></tr></tbody></table></td> <td width="235"> <table colspan="4" rowspan="5" bgcolor="#939393" border="0" cellspacing="1" width="235"> <colgroup> <col width="53"> <col width="50"> <col width="69"> <col width="63"> </colgroup><tbody bgcolor="#ffffff"> <tr height="18"> <td style="font-family: verdana;" align="middle" bgcolor="#dcdcdc">B</td> <td style="font-family: verdana;" align="middle" bgcolor="#dcdcdc">C</td> <td style="font-family: verdana;" align="middle" bgcolor="#dcdcdc">D</td> <td style="font-family: verdana;" align="middle" bgcolor="#dcdcdc">E</td></tr> <tr height="18"> <td align="left" bgcolor="#ffffff">Value</td> <td align="left" bgcolor="#ffffff">Rank</td> <td align="left" bgcolor="#ffffff">Desired</td> <td align="left" bgcolor="#ffffff">Answer</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">120</td> <td align="right" bgcolor="#ffffff">1</td> <td align="right" bgcolor="#ffffff">1</td> <td align="right" bgcolor="#ffff00">1</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">125</td> <td align="right" bgcolor="#ffffff">3</td> <td align="right" bgcolor="#ffffff">2</td> <td align="right" bgcolor="#ffff00">2</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">130</td> <td align="right" bgcolor="#ffffff">5</td> <td align="right" bgcolor="#ffffff">3</td> <td align="right" bgcolor="#ffff00">3</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">130</td> <td align="right" bgcolor="#ffffff">5</td> <td align="right" bgcolor="#ffffff">3</td> <td align="right" bgcolor="#ffff00">3</td></tr></tbody></table> <table colspan="4" rowspan="6" bgcolor="#939393" border="0" cellspacing="1" width="235"> <colgroup> <col width="53"> <col width="50"> <col width="69"> <col width="63"> </colgroup><tbody bgcolor="#ffffff"> <tr height="18"> <td align="right" bgcolor="#ffffff">142</td> <td align="right" bgcolor="#ffffff">9</td> <td align="right" bgcolor="#ffffff">4</td> <td align="right" bgcolor="#ffff00">4</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">120</td> <td align="right" bgcolor="#ffffff">1</td> <td align="right" bgcolor="#ffffff">1</td> <td align="right" bgcolor="#ffff00">1</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">125</td> <td align="right" bgcolor="#ffffff">3</td> <td align="right" bgcolor="#ffffff">2</td> <td align="right" bgcolor="#ffff00">2</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">130</td> <td align="right" bgcolor="#ffffff">5</td> <td align="right" bgcolor="#ffffff">3</td> <td align="right" bgcolor="#ffff00">3</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">130</td> <td align="right" bgcolor="#ffffff">5</td> <td align="right" bgcolor="#ffffff">3</td> <td align="right" bgcolor="#ffff00">3</td></tr> <tr height="18"> <td align="right" bgcolor="#ffffff">142</td> <td align="right" bgcolor="#ffffff">9</td> <td align="right" bgcolor="#ffffff">4</td> <td align="right" bgcolor="#ffff00">4</td></tr></tbody></table></td></tr></tbody></table>
<colspan=12 rowspan="5" height="90" width="12"> </colspan=12><table style="border-collapse: collapse;" border="6" cellspacing="0"><tbody> <tr height="24"> <td colspan="12" align="middle">Used Formula ...(With Running MicrosoftExcel Ver 2003) </td></tr><tr height="24"> <td align="middle" bgcolor="#d3d3d3" width="4%">No</td> <td align="middle" bgcolor="#d3d3d3" width="9%">Addr'</td> <td align="middle" bgcolor="#d3d3d3" width="65%"> If use below Formula, You'll Get Result as Right</td> <td align="middle" bgcolor="#d3d3d3" width="15%">Result</td> <td align="middle" bgcolor="#d3d3d3">Etc</td> </tr><tr height="20"> <td align="middle">1</td> <td align="middle">C2</td> <td>=RANK(B2,\$B\$2:\$B\$11,-1) </td> <td align="right">1</td> <td>
</td> </tr><tr height="20"> <td align="middle">2</td> <td align="middle">
</td> <td>C2 His Formula Used This Cell -> C2:C11
</td> <td align="left">
</td> <td>
</td> </tr><tr height="20"> <td align="middle">3</td> <td align="middle">E2</td> <td>=SUMPRODUCT((\$B\$2:\$B\$11< =B2)*(MATCH(\$B\$2:\$B\$11,\$B\$2:\$B\$11,0)=(ROW(\$B\$2:\$B\$11)-ROW(\$B\$2)+1))) </td> <td align="right">1</td> <td>
</td> </tr><tr height="20"> <td align="middle">4</td> <td align="middle">
</td> <td>E2 His Formula Used This Cell -> E2:E11
</td> <td align="left">
</td> <td>
</td></tr> <tr height="24"> <td colspan="12">
</td></tr></tbody></table>

Last edited:

#### fairwinds

##### MrExcel MVP
It would be sorted in ascending order.

=(A2>N(A1))+N(B1) in B2, drag down.

#### facethegod

##### Well-known Member
I have a list of values (some are duplicates) which I need to rank.

The problem arises where I have more than one of any rank, the next rank skips a number. I need to have a list of sequential numbers and therefore can't have any numbers missing.

Example:
Value Rank Desired Rank
120 1 1
125 2 2
130 3 3
130 3 3
142 5 4

Any ideas?
This should work for an unsorted set:

=SUMPRODUCT(--(COUNTIF(OFFSET(\$A\$1,,,ROW(\$A\$1:\$A\$9)-ROW(\$A\$1)+1),\$A\$1:\$A\$9)=1),--(\$A\$1:\$A\$9<=A1))

#### sassa

##### New Member
This should work for an unsorted set:

=SUMPRODUCT(--(COUNTIF(OFFSET(\$A\$1,,,ROW(\$A\$1:\$A\$9)-ROW(\$A\$1)+1),\$A\$1:\$A\$9)=1),--(\$A\$1:\$A\$9<=A1))
Would it be possible to edit the above formula so that it ranks the highest value in the array as 1, the second highest value in the array as 2 and so on? If I am not mistaken the formula ranks from lowest to highest....

Thanks!

1,101,802
Messages
5,482,982
Members
407,371
Latest member
gdjenkins80

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...