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

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

Hi Max

Thanks for the help but here is the thing ... Some of the addresses that are
a correct match is giving a FALSE result. I did some checks on the ones that
came out FALSE and noticed that. The only difference in the address ...is
that one is upper case and the other lower (i.e. sheet 2 addresses are all in
upper case) but some of them are match and reflect TRUE so I cannot find the
trend.
--
AAH


"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

MATCH is not case sensitive, so case is not an issue. It's more likely that
there are extraneous white spaces here & there in your data which is throwing
the "correct" matching off. TRIM could take care of these and improve the
robustness of matching.

Try it again with TRIM, like this:

In Sheet1's F4, array-entered:
=IF(COUNTA(D4:E4)=0,"",ISNUMBER(MATCH(1,(TRIM(Shee t2!C$4:C$7379)=TRIM(D4))*(TRIM(Sheet2!D$4:D$7379)= TRIM(E4)),0)))

In Sheet2's E4, array-entered:
=IF(COUNTA(C4:D4)=0,"",ISNUMBER(MATCH(1,(TRIM(Shee t1!D$4:D$7379)=TRIM(C4))*(TRIM(Sheet1!E$4:E$7379)= TRIM(D4)),0)))

Btw, please reply directly to my response instead of replying to your own
posting. That's the correct way to carry on discussions in these newsgroups.
Also, take a moment to press the "Yes" button below. Doing so enhances thread
longevity for the general benefit of other readers, and brings nice green
colours up.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"AAH" wrote:
Hi Max

Thanks for the help but here is the thing ... Some of the addresses that are
a correct match is giving a FALSE result. I did some checks on the ones that
came out FALSE and noticed that. The only difference in the address ...is
that one is upper case and the other lower (i.e. sheet 2 addresses are all in
upper case) but some of them are match and reflect TRUE so I cannot find the
trend.
--
AAH

Thanks again I will try it and let you know.
--
AAH


"Max" wrote:

MATCH is not case sensitive, so case is not an issue. It's more likely that
there are extraneous white spaces here & there in your data which is throwing
the "correct" matching off. TRIM could take care of these and improve the
robustness of matching.

Try it again with TRIM, like this:

In Sheet1's F4, array-entered:
=IF(COUNTA(D4:E4)=0,"",ISNUMBER(MATCH(1,(TRIM(Shee t2!C$4:C$7379)=TRIM(D4))*(TRIM(Sheet2!D$4:D$7379)= TRIM(E4)),0)))

In Sheet2's E4, array-entered:
=IF(COUNTA(C4:D4)=0,"",ISNUMBER(MATCH(1,(TRIM(Shee t1!D$4:D$7379)=TRIM(C4))*(TRIM(Sheet1!E$4:E$7379)= TRIM(D4)),0)))

Btw, please reply directly to my response instead of replying to your own
posting. That's the correct way to carry on discussions in these newsgroups.
Also, take a moment to press the "Yes" button below. Doing so enhances thread
longevity for the general benefit of other readers, and brings nice green
colours up.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"AAH" wrote:
Hi Max

Thanks for the help but here is the thing ... Some of the addresses that are
a correct match is giving a FALSE result. I did some checks on the ones that
came out FALSE and noticed that. The only difference in the address ...is
that one is upper case and the other lower (i.e. sheet 2 addresses are all in
upper case) but some of them are match and reflect TRUE so I cannot find the
trend.
--
AAH

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"AAH" wrote in message
...
Thanks again I will try it and let you know.
--
AAH

Hi Max

I am now getting a "too many arguments for this function" error, then it
highlights the zero at the end of the function.
--
AAH


"Max" wrote:

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik
---
"AAH" wrote in message
...
Thanks again I will try it and let you know.
--
AAH

Try copying the formulas directly from my response and paste directly into
the formula bar in your sheet, then press CTRL+SHIFT+ENTER to confirm it.

Attached is a sample with the earlier 2 array formulas implemented:
http://www.freefilehosting.net/download/3jeie
AAH.xls

You can copy n paste directly from the sample's formula bar into your actual
sheet's formula bar.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:15,700 Files:353 Subscribers:53
xdemechanik
---
"AAH" wrote in message
...
Hi Max

I am now getting a "too many arguments for this function" error, then it
highlights the zero at the end of the function.
--
AAH