Conditional formatting - highlight text cells based on values other column

[smide]

Hello.


In columns A, B and C (A2:C600) I have a lists of different towns (column A), customer names (column B) and buying price for each customer in column C.


In columns D and E I'm receiving new list (towns and customers).


I'm monitoring top 3 customers from each town (top 3 buying prices) and I would like to mark cells for those top 3 cutomer names (column B) IF those names do not appear on new list (columns D and E).


*note: same name could appear in two different towns

example.


[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Town1[/TD]
[TD]Name1[/TD]
[TD]9[/TD]
[TD]Town1[/TD]
[TD]Name1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Town1[/TD]
[TD]Name2[/TD]
[TD]5[/TD]
[TD]Town1[/TD]
[TD]Name2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Town1[/TD]
[TD]Name3[/TD]
[TD]12[/TD]
[TD]Town1[/TD]
[TD]Name4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Town1[/TD]
[TD]Name4[/TD]
[TD]25[/TD]
[TD]Town1[/TD]
[TD]Name5[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Town1[/TD]
[TD]Name5[/TD]
[TD]0[/TD]
[TD]Town2[/TD]
[TD]Name15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Town2[/TD]
[TD]Name6[/TD]
[TD]11[/TD]
[TD]Town2[/TD]
[TD]Name8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Town2[/TD]
[TD]Name7[/TD]
[TD]4[/TD]
[TD]Town2[/TD]
[TD]Name6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]Town2[/TD]
[TD]Name8[/TD]
[TD]2[/TD]
[TD]Town3[/TD]
[TD]....[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Town2[/TD]
[TD]Name9[/TD]
[TD]19[/TD]
[TD]....[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Town2[/TD]
[TD]Name10[/TD]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]Town2[/TD]
[TD]Name11[/TD]
[TD]28[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Town3[/TD]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]....[/TD]
[TD]....[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


explanation:

- top 3 customers from Town1 are Name4 (25), Name3 (12) and Name1 (9), in new list (columns D and E) customer from top 3 Name3 does not appear so highlight cell containing Name3 in column B (bold text)
- top 3 customers from Town2 are Name11 (28), Name9 (19) and Name6 (11), in new list (columns D and E) customers from top 3 Name11 and Name9 both does not appear so higlight both cells in column B (bold text)

 

[Snakehips]

smide, try this....

Edit the following formula and change my 200 row max of ranges to be your expected maximum rows.
Select your desired range in column B and Conditional Formatting > New Rule > Use Formula

Paste in your revised formula and set the bold font format.

  =AND(SUMPRODUCT(($D$2:$D$200&$E$2:$E$200=IF(C2>LARGE((A$2:A$200=A2)*C$2:C$200,4),A2&B2,""))*1) =0,IF(C2>LARGE((A$2:A$200=A2)*C$2:C$200,4),A2&B2,"")>"").

Hope that helps.

 

[Peter_SSs]

Here is an alternative CF formula after selecting B2:Bxx.

Excel Workbook
	A	B	C	D	E
1
2	Town1	Name1	9	Town1	Name1
3	Town1	Name2	5	Town1	Name2
4	Town1	Name3	12	Town1	Name4
5	Town1	Name4	25	Town1	Name5
6	Town1	Name5	0	Town2	Name15
7	Town2	Name6	11	Town2	Name8
8	Town2	Name7	4	Town2	Name6
9	Town2	Name8	2	Town3
10	Town2	Name9	19
11	Town2	Name10	7
12	Town2	Name11	28
13
CF

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
B2	1. / Formula is =AND(B2<>"",C2>=AGGREGATE(14,6,C$2:C$600/(A$2:A$600=A2),3),COUNTIFS(D$2:D$600,A2,E$2:E$600,B2)=0)	Abc