Rank values in one column from groups of numbers in another column

[fruit&veg]

Hi all, so down 20 rows of column A i have ten rows of '1', five rows of '2' and five rows of '3'. column B has various decimal numbers from -10 to 50. I am looking for a formula (for column C) that ranks the numbers (ascending) in column B, taking into account the corresponding number in column A.

So, the B numbers alongside the column A '1's should result in being ranked (1 to 10), the numbers alongside the '2's should be ranked (1 to 5) and the numbers alongside the '3's would be ranked (1 to 5).

(The above values are merely an example, the final table would have numbers running into the thousands down column A, with each number duplicated up to 200 times.)

Any help would be much appreciated,

Kind regards

[Bernie Deitrick]

In B2, enter the formula

=SUMPRODUCT(($A$2:$A$20000=A2)*($A$2:$A$20000+$B$2:$B$20000<(A2+B2)))+1
to rank lower values as lower rank number
or
=SUMPRODUCT(($A$2:$A$20000=A2)*($A$2:$A$20000+$B$2:$B$20000>(A2+B2)))+1
to rank higher values as lower rank number

and copy down. If you have more than 20000 rows of data, increase those numbers, and if many fewer, reduce them.

If you can have ties that need to be broken, a modified formula with a tie-breaker can be used.

[fruit&veg]

Bernie you're an absolute star, thank you so much, its highly appreciated. The tied issue shouldn't be a problem (i am into double figure decimal points in the B column), but thank you anyway.

Kind regards

[Bernie Deitrick]

I just realized that I over-complicated the formula

=SUMPRODUCT(($A$2:$A$20000=A2)*($B$2:$B$20000<B2))+1
to rank lower values as lower rank number
or
=SUMPRODUCT(($A$2:$A$20000=A2)*($B$2:$B$20000>B2))+1
to rank higher values as lower rank number