Presume your line: .. C4:C5431
should actually read: .. C4:C7379

One way to set up the comparisons

In Sheet1,
Put in F4, array-enter*:
=IF(COUNTA(D4:E4)=0,"",ISNUMBER(MATCH(1,(Sheet2!C$ 4:C$7379=D4)*(Sheet2!D$4:D$7379=E4),0)))
Copy down as far as required. Col F returns TRUE, FALSE or blank: "",
depending on whether the addresses in cols D & E are found in Sheet2's cols C
& D or otherwise. You could then easily apply autofilter on col F to retrieve
as desired, or apply CF pointing to col F.

*To array-enter means to press CTRL+SHIFT+ENTER to confirm the formula

Repeat likewise for the converse checks in Sheet2 ..

In Sheet2,
Put in say, E4, array-enter:
=IF(COUNTA(C4:D4)=0,"",ISNUMBER(MATCH(1,(Sheet1!D$ 4:D$7379=C4)*(Sheet1!E$4:E$7379=D4),0)))
Copy down as far as required. Col E returns TRUE, FALSE or blank: "",
depending on whether the addresses in cols C & D are found in Sheet1's cols D
& E or otherwise. You could then easily apply autofilter on col E to retrieve
as desired, or apply CF pointing to col E
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"AAH" wrote:
I am trying to compare addresses from two different worksheets in a WorkBook.
Worksht one D4:D7379 (Address line 1), E4:E7379 (Address line 2) and
Worksht two C4:C5431 (Address line 1), D4:D7379 (Address line 2). I want to
copy the cells that are not a match to be Highligted with RED.
--
AAH