Using rank with duplicative values

[sjuhl99]

Hi, I am trying to rank the data below using the duplicative values in column A. So for instance 1061012 has 3 values in column B (10.87%, 11.89% and 10.66%), i want to rank those 1-3 then start over with the values for 1061414 (1-3). I have written some count ifs but not sure how to rank column B within that. My data set is much bigger than below, just a snipet of data. Please help if you can. thanks



[TABLE="width: 215"]
<colgroup><col><col></colgroup><tbody>[TR]
[TD]FCST_1_ID[/TD]
[TD]% Customer DMD (Base Units)[/TD]
[/TR]
[TR]
[TD]1061412[/TD]
[TD="align: right"]10.87%[/TD]
[/TR]
[TR]
[TD]1061412[/TD]
[TD="align: right"]11.89%[/TD]
[/TR]
[TR]
[TD]1061412[/TD]
[TD="align: right"]10.66%[/TD]
[/TR]
[TR]
[TD]1061414[/TD]
[TD="align: right"]11.06%[/TD]
[/TR]
[TR]
[TD]1061414[/TD]
[TD="align: right"]13.42%[/TD]
[/TR]
[TR]
[TD]1061414[/TD]
[TD="align: right"]12.66%[/TD]
[/TR]
[TR]
[TD]1061415[/TD]
[TD="align: right"]9.32%[/TD]
[/TR]
[TR]
[TD]1061415[/TD]
[TD="align: right"]12.84%[/TD]
[/TR]
[TR]
[TD]1061415[/TD]
[TD="align: right"]11.54%[/TD]
[/TR]
</tbody>[/TABLE]

 

[DanteAmor]

The data must be sorted by column A and must start in row 2

Use the following formula

[TABLE="class: grid, width: 950"]
<tbody>[TR]
[TD]Cell[/TD]
[TD]Formula[/TD]
[/TR]
[TR]
[TD]C2[/TD]
[TD]=RANK(B2,INDIRECT("B"& MATCH(A2,$A$1:$A$10,0) & ":B" & SUMPRODUCT(MAX(($A$2:$A$10=A2)*(ROW($A$2:$A$10))))))[/TD]
[/TR]
</tbody>[/TABLE]

Change 10 to the last row number with data. Then copy the formula down.

 

[sjuhl99]

Thank you so much, it worked perfectly!

 

[DanteAmor]

I'm glad to help you