I really Need help with Formula (Excel 2003)

[Guest]

I have email addresses in Column A & Column B and I'm trying to find matching
addresses between both columns. I have tried the following formulas however,
they are not counting the matching items correctly:
=ISNA(MATCH(B2,$A$2:$A4661,FALSE)) and I have tried this:
=SUMPRODUCT(($A$2:$A$662=A2)--($B$2:$B$662=B2))

What am I doing wrong?

Thanks for the help,

 

[Guest]

=SUMPRODUCT(COUNTIF(A1:A9,B1:B9))

 

[Guest]

What do want the result to be if you find a match?

e.g if A3 = B20 what answer do you want (and where)?

in C2:

=IF(ISNA(MATCH(B2,$A$2:$A$600,0)),"No match","Match in A"
&MATCH(B2,$A$2:$A$600,0))

HTH

 

[Guest]

I get 0 when using this formula. What am I doing wrong

 

[Guest]

I get No Match when using this formula.

Thanks,

What do want the result to be if you find a match?

e.g if A3 = B20 what answer do you want (and where)?

in C2:

=IF(ISNA(MATCH(B2,$A$2:$A$600,0)),"No match","Match in A"
&MATCH(B2,$A$2:$A$600,0))

HTH

 

[Peo Sjoblom]

It means there are no duplicates, you might have invisible characters in one
or the other like trailing/leading spaces. Test the formula on a small
range, put in some values yourself that you are sure are duplicates and you
will see that it works.

 

[Guest]

I have cleared the formatting from both columns and they are both formatted
the same (i.e. (e-mail address removed)). There are many duplicates in
both columns.

 

[Peo Sjoblom]

That's not what I said! I said invible characters like trailing/leading
spaces, invisible html characters.
Formatting has nothing to do with invisible characters.

try

=A1=B1


where A1 and B1 are 2 cells you know are duplicates, if you get FALSE then
they are not duplicates regardless of formatting

 

[Guest]

See Peo's response re data. Formula does work!

I get No Match when using this formula.

Thanks,

 

[Guest]

The formula is returning NO MATCH for all 600 rows when I know there many
duplicate emails. I copied the formula exactly the way Peo wrote it and it
is not showing any MATCH.

Any ideas when I can be doing wrong.

 

[Guest]

I know what invisible characters are and I ran the Clean function and pasted
the values over. Please note that the information that repeats are not
exactly in the same row. ([email protected]) could be in A2 and also
repeated in B600 so the repeated items are not side by side.

As far as your suggestion (=a1=b1) I get false all the way across, but again
the repeated items are not in the same row.

Thanks,

 

[Peo Sjoblom]

You shouldn't use =A1=B1, use it on a 2 cells that you know are duplicates
(>> where A1 and B1 are 2 cells you know are duplicates,) was what I said
if you get FALSE then you know they are not duplicates

The CLEAN function will not clean invisible html characters

 

[Peo Sjoblom]

You are not doing anything wrong, it's your data that is not exact
duplicates although they may look like that. Install and run David
McRitchie's TRIMALL macro on both ranges, it should work after that

http://www.mvps.org/dmcritchie/excel/join.htm#trimall

 

[Guest]

Hi,

try this may be it works:

=IF(ISNA(VLOOKUP(B2,$A$2:$A$600,1,false),"",1)

Thanks,

 

[Guest]

What Peo is getting at, if A2 is repeated in B600, then type

=A2=B600

in an empty cell. If you get False, then the two cells are not equal, which
is why none of the suggestions are working. =A1=B1 was only an example.

 

[Guest]

I understand much better now and I tried the sample below with two cells that
had the same email address and it returned FALSE. I'm not sure what may be
going on but I'll take a look at the formatting further. If anyone has any
suggestions for me please let me know

thanks,