Compare data in three sets of columns, highlight non matches

[valmir]

Hello Everyone
I have three sets of data of two columns each (names and numbers)
First set of two columns has nothing to compare with. Second set compares to the first. Third set compares to the second.
If any row in second set has no match in any row in the first set, it must be highlighted. If any If any row in third set has no match in any row in the second set, it must be highlighted.
Solution should be generic (no specific names) as I intend to use it across other similar data.
I am attaching a sample file in which I have manually highlighted the data, with my intended outcome.

Top Scorers.xlsx
	A	B	C	D	E	F	G	H
1	Richard	3		Daniel	3		Richard	5
2	James	2		Richard	3		Donald	4
3	Charles	1		James	2		Daniel	3
4	Christopher	1		Robert	2		James	3
5	Daniel	1		Andrew	1		Claude	2
6	David	1		Anthony	1		David	2
7	John	1		Charles	1		Mark	2
8	Joseph	1		Christopher	1		Robert	2
9	Michael	1		David	1		William	2
10	Robert	1		Donald	1		Andrew	1
11	Thomas	1		John	1		Anthony	1
12	William	1		Joseph	1		Ben	1
13				Mark	1		Bryan	1
14				Michael	1		Charles	1
15				Paul	1		Christopher	1
16				Steven	1		George	1
17				Thomas	1		Jason	1
18				William	1		John	1
19							Joseph	1
20							Joshua	1
21							Kevin	1
22							Malcom	1
23							Martin	1
24							Michael	1
25							Paul	1
26							Steven	1
27							Thomas	1
Scorers

 

[Eric W]

How about:

Book1
	A	B	C	D	E	F	G	H
1	Richard	3		Daniel	3		Richard	5
2	James	2		Richard	3		Donald	4
3	Charles	1		James	2		Daniel	3
4	Christopher	1		Robert	2		James	3
5	Daniel	1		Andrew	1		Claude	2
6	David	1		Anthony	1		David	2
7	John	1		Charles	1		Mark	2
8	Joseph	1		Christopher	1		Robert	2
9	Michael	1		David	1		William	2
10	Robert	1		Donald	1		Andrew	1
11	Thomas	1		John	1		Anthony	1
12	William	1		Joseph	1		Ben	1
13				Mark	1		Bryan	1
14				Michael	1		Charles	1
15				Paul	1		Christopher	1
16				Steven	1		George	1
17				Thomas	1		Jason	1
18				William	1		John	1
19							Joseph	1
20							Joshua	1
21							Kevin	1
22							Malcom	1
23							Martin	1
24							Michael	1
25							Paul	1
26							Steven	1
27							Thomas	1
28
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D,G:G	Expression	=AND(COUNTIFS(A:A,D1,B:B,E1)=0,D1<>"")	text	NO

 

[Fluff]

For col D how about

=AND($D1<>"",COUNTIFS($A:$A,$D1,$B:$B,$E1)=0)

 

[valmir]

How about:

Book1
	A	B	C	D	E	F	G	H
1	Richard	3		Daniel	3		Richard	5
2	James	2		Richard	3		Donald	4
3	Charles	1		James	2		Daniel	3
4	Christopher	1		Robert	2		James	3
5	Daniel	1		Andrew	1		Claude	2
6	David	1		Anthony	1		David	2
7	John	1		Charles	1		Mark	2
8	Joseph	1		Christopher	1		Robert	2
9	Michael	1		David	1		William	2
10	Robert	1		Donald	1		Andrew	1
11	Thomas	1		John	1		Anthony	1
12	William	1		Joseph	1		Ben	1
13				Mark	1		Bryan	1
14				Michael	1		Charles	1
15				Paul	1		Christopher	1
16				Steven	1		George	1
17				Thomas	1		Jason	1
18				William	1		John	1
19							Joseph	1
20							Joshua	1
21							Kevin	1
22							Malcom	1
23							Martin	1
24							Michael	1
25							Paul	1
26							Steven	1
27							Thomas	1
28
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D,G:G	Expression	=AND(COUNTIFS(A:A,D1,B:B,E1)=0,D1<>"")	text	NO

Hi Eric! I used the exact same formula in G1 but it highlights only up until row 16. Meaning I didn't get the result you've shown. Do I have to change anything in the formula?

 

[valmir]

For col D how about

=AND($D1<>"",COUNTIFS($A:$A,$D1,$B:$B,$E1)=0)

Thanks Fluff. It works perfectly for column D. Now, what about column G?

 

[valmir]

Thanks Fluff. It works perfectly for column D. Now, what about column G?

Ok, I followed the logic and I got it! Thanks!

 

[Fluff]

Glad you sorted it & thanks for the feedback.

 

[valmir]

Glad you sorted it & thanks for the feedback.

Thanks a lot! Both formulas work perfectly! I'll mark Eric's as a solution because he was the first one to reply!

 

[valmir]

How about:

Book1
	A	B	C	D	E	F	G	H
1	Richard	3		Daniel	3		Richard	5
2	James	2		Richard	3		Donald	4
3	Charles	1		James	2		Daniel	3
4	Christopher	1		Robert	2		James	3
5	Daniel	1		Andrew	1		Claude	2
6	David	1		Anthony	1		David	2
7	John	1		Charles	1		Mark	2
8	Joseph	1		Christopher	1		Robert	2
9	Michael	1		David	1		William	2
10	Robert	1		Donald	1		Andrew	1
11	Thomas	1		John	1		Anthony	1
12	William	1		Joseph	1		Ben	1
13				Mark	1		Bryan	1
14				Michael	1		Charles	1
15				Paul	1		Christopher	1
16				Steven	1		George	1
17				Thomas	1		Jason	1
18				William	1		John	1
19							Joseph	1
20							Joshua	1
21							Kevin	1
22							Malcom	1
23							Martin	1
24							Michael	1
25							Paul	1
26							Steven	1
27							Thomas	1
28
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
D:D,G:G	Expression	=AND(COUNTIFS(A:A,D1,B:B,E1)=0,D1<>"")	text	NO

Thanks a lot Eric. It works perfectly!

 

[Eric W]

Glad we could help!