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

FCST_1_ID	% Customer DMD (Base Units)
1061412	10.87%
1061412	11.89%
1061412	10.66%
1061414	11.06%
1061414	13.42%
1061414	12.66%
1061415	9.32%
1061415	12.84%
1061415	11.54%

<colgroup><col><col></colgroup><tbody>
</tbody>

 

[DanteAmor]

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

Use the following formula

Cell	Formula
C2	=RANK(B2,INDIRECT("B"& MATCH(A2,$A$1:$A$10,0) & ":B" & SUMPRODUCT(MAX(($A$2:$A$10=A2)*(ROW($A$2:$A$10))))))

<tbody>
</tbody>

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