Comparing Addresses from 2 different Worksheets

[AAH]

I am trying to compare addresses from two different worksheets in a WorkBook.
Worksht one D47379 (Address line 1), E4:E7379 (Address line 2) and
Worksht two C4:C5431 (Address line 1), D47379 (Address line 2). I want to
copy the cells that are not a match to be Highligted with RED.

 

[Max]

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$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(C44)=0,"",ISNUMBER(MATCH(1,(Sheet1!D$4$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]

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.

 

[Max]

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(Sheet2!C$4:C$7379)=TRIM(D4))*(TRIM(Sheet2!D$4$7379)=TRIM(E4)),0)))

In Sheet2's E4, array-entered
=IF(COUNTA(C44)=0,"",ISNUMBER(MATCH(1,(TRIM(Sheet1!D$4$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]

Thanks again I will try it and let you know.

 

[Max]

Welcome
--
Max
Singapore
http://savefile.com/projects/236895
Downloads: 15,500, Files: 352, Subscribers: 53
xdemechanik

 

[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.

 

[Max]

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