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?

 

[anaBonzi]

this formula not working, are their any reason or tips

<tbody bgcolor="#ffffff">
[TD="colspan: 2"] DataSheet= SampleSheet[/TD]

[TD="align: right"] [TABLE="width: 30"]
<tbody bgcolor="#ffffff"> [TR]

[TD="bgcolor: #dcdcdc, align: right"]1 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]2 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]3 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]4 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]5 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]6 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]7 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]8 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]9 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]10 [/TD]

[TD="bgcolor: #dcdcdc, align: right"]11 [/TD]

</tbody>

[/TD]
[TD="width: 235"] [TABLE="width: 235"]
<colgroup> <col width="53"> <col width="50"> <col width="69"> <col width="63"> </colgroup><tbody bgcolor="#ffffff"> [TR]
[TD="bgcolor: #dcdcdc"]B[/TD]
[TD="bgcolor: #dcdcdc"]C[/TD]
[TD="bgcolor: #dcdcdc"]D[/TD]
[TD="bgcolor: #dcdcdc"]E[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: left"]Value[/TD]
[TD="bgcolor: #ffffff, align: left"]Rank[/TD]
[TD="bgcolor: #ffffff, align: left"]Desired[/TD]
[TD="bgcolor: #ffffff, align: left"]Answer[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]120[/TD]
[TD="bgcolor: #ffffff, align: right"]1[/TD]
[TD="bgcolor: #ffffff, align: right"]1[/TD]
[TD="bgcolor: #ffff00, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]125[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffffff, align: right"]2[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]130[/TD]
[TD="bgcolor: #ffffff, align: right"]5[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]130[/TD]
[TD="bgcolor: #ffffff, align: right"]5[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 235"]
<colgroup> <col width="53"> <col width="50"> <col width="69"> <col width="63"> </colgroup><tbody bgcolor="#ffffff"> [TR]
[TD="bgcolor: #ffffff, align: right"]142[/TD]
[TD="bgcolor: #ffffff, align: right"]9[/TD]
[TD="bgcolor: #ffffff, align: right"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]4[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]120[/TD]
[TD="bgcolor: #ffffff, align: right"]1[/TD]
[TD="bgcolor: #ffffff, align: right"]1[/TD]
[TD="bgcolor: #ffff00, align: right"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]125[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffffff, align: right"]2[/TD]
[TD="bgcolor: #ffff00, align: right"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]130[/TD]
[TD="bgcolor: #ffffff, align: right"]5[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]130[/TD]
[TD="bgcolor: #ffffff, align: right"]5[/TD]
[TD="bgcolor: #ffffff, align: right"]3[/TD]
[TD="bgcolor: #ffff00, align: right"]3[/TD]
[/TR]
[TR]
[TD="bgcolor: #ffffff, align: right"]142[/TD]
[TD="bgcolor: #ffffff, align: right"]9[/TD]
[TD="bgcolor: #ffffff, align: right"]4[/TD]
[TD="bgcolor: #ffff00, align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

<colspan=12 rowspan="5" height="90" width="12"> </colspan=12>

1	C2	=RANK(B2,$B$2:$B$11,-1)
2		C2 His Formula Used This Cell -> C2:C11
3	E2	=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)))
4		E2 His Formula Used This Cell -> E2:E11

<tbody>
[TD="colspan: 12"] Used Formula ...(With Running MicrosoftExcel Ver 2003) [/TD]

[TD="bgcolor: #d3d3d3"] No [/TD]
[TD="width: 9%, bgcolor: #d3d3d3"] Addr' [/TD]
[TD="width: 65%, bgcolor: #d3d3d3"] If use below Formula, You'll Get Result as Right [/TD]
[TD="width: 15%, bgcolor: #d3d3d3"] Result [/TD]
[TD="bgcolor: #d3d3d3"]Etc[/TD]

[TD="align: right"] 1 [/TD]

[TD="align: left"][/TD]

[TD="align: right"] 1 [/TD]

[TD="align: left"][/TD]

[TD="colspan: 12"][/TD]

</tbody>