Rank Duplicate values

[Clare1805]

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?

 

[fairwinds]

Hi,

With your list in A2:A9,

Try:

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

in B2 dragged down.

 

[Clare1805]

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]

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

 

[Clare1805]

It would be sorted in ascending order.

Thanks for your help.

 

[theozz]

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

 

[fairwinds]

It would be sorted in ascending order.

Thanks for your help.

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

 

[facethegod]

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]

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!

 

[VoG]

This thread is ancient - please start a new one.