Thread: Uniquely rank based on two criteria

I am trying to rank based on a dollar value within a specific category (in this case a product #) and then show those dollar values in order in a separate location.

Please see below. I would like to be able to take hundreds of entries (first table) and as more are added, have the second table automatically update showing unique offers by $ ranked in order.

I have been trying to first rank all the offers by two criteria, but have been unable to show unique ranks. The goal would then to use that rank to populate the second sheet somehow.

Is there a formula that can accomplish this? I am open to suggestions.

Thank you in advance.


Product ID Offer $
123456789 $50.00
123456789 $20.00
123456789 $50.00
123456789 $20.00
987654321 $10.00
123456789 $60.00
123456789 $25.00
987654321 $12.00
987654321 $10.00

Rank 1 2 3 4
123456789 $60 $50 $50 $25
987654321 $12 $ 10 $ 10

if the raw data is in Sheet1, columns A:B

then in Sheet2, list unique IDs in Column A and in B2 enter:

=IF(COUNTIF(Sheet1!$A$2:$A$10,$A2)<COLUMNS(Sheet1!$A$1:A$1),"",LARGE(IF(Sheet1!$A$2:$A$10=$A2,Sheet1!$B$2:$B$10),COLUMNS(Sheet1!$A$1:A$1)))

adjusting ranges to suit and confirm with CTRL+SHIFT+ENTER not just enter,

Then copy down as far as you need and across as far as you desire to ensure you get all possible results.

You have not included some critical $ signs...

try:

=IF(COUNTIF($A$2:$A$10,$H2)<COLUMNS($A$1:A$1)," ",LARGE(IF($A$2:$A$10=$H2,$B$2:$B$10),COLUMNS($A$1:A$1)))

confirmed with CSE and copied across and down