Highlight top values based on text in specific column

[smide]

Hello.




In column B (B3:B600) of my Workbook I have a list of different towns (about 25 towns) and in column C (C3:C600) customer names.


In column F (F3:F600) there are buying prices for each customer.


I need to highlight (color red) cells with top FIVE customer names (column C) based on buying price value and town name for each town.


example.

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name1[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]15[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name2[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]2[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name3[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]10[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name4[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]0[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name5[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]6[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name6[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"] 24[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD][/TD]
[TD]Town1[/TD]
[TD]Name7[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]9[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD][/TD]
[TD]Town2 (new town)[/TD]
[TD]Name8[/TD]
[TD][/TD]
[TD][/TD]
[TD] 7[/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD]....[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: center"]....[/TD]
[/TR]
</tbody>[/TABLE]

​

highlighted top FIVE customer names for Town1 (based on values in column F): C3, C5, C7, C8 and C9

 

[Misca]

Try using conditional formatting with a formula like

=SUMPRODUCT(--($B$2:$B$101=$B2),--($F$2:$F$101>$F2))<5

The sumproduct counts how many rows with the matching town name have a bigger value in column F.