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.

	A	B	C	D	E	F
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	Town3	...
14	....	....

<tbody>
</tbody>

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