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,

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


"Marilyn" wrote:

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,

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

"Teethless mama" wrote:

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


"Marilyn" wrote:

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,

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.


--
Regards,

Peo Sjoblom


"Marilyn" wrote in message
...
I get 0 when using this formula. What am I doing wrong

"Teethless mama" wrote:

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


"Marilyn" wrote:

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,

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

"Peo Sjoblom" wrote:

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.


--
Regards,

Peo Sjoblom


"Marilyn" wrote in message
...
I get 0 when using this formula. What am I doing wrong

"Teethless mama" wrote:

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


"Marilyn" wrote:

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,

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


--
Regards,

Peo Sjoblom


"Marilyn" wrote in message
...
I have cleared the formatting from both columns and they are both formatted
the same (i.e. ). There are many duplicates in
both columns.

"Peo Sjoblom" wrote:

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.


--
Regards,

Peo Sjoblom


"Marilyn" wrote in message
...
I get 0 when using this formula. What am I doing wrong

"Teethless mama" wrote:

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


"Marilyn" wrote:

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,

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



"Marilyn" wrote:

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,

I get No Match when using this formula.

Thanks,

"Toppers" wrote:

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



"Marilyn" wrote:

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,

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

"Marilyn" wrote:

I get No Match when using this formula.

Thanks,

"Toppers" wrote:

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



"Marilyn" wrote:

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,

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.

"Toppers" wrote:

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

"Marilyn" wrote:

I get No Match when using this formula.

Thanks,

"Toppers" wrote:

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



"Marilyn" wrote:

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,

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



--
Regards,

Peo Sjoblom



"Marilyn" wrote in message
...
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.

"Toppers" wrote:

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

"Marilyn" wrote:

I get No Match when using this formula.

Thanks,

"Toppers" wrote:

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



"Marilyn" wrote:

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,

Hi,

try this may be it works:

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

Thanks,
--
Farhad Hodjat


"Marilyn" wrote:

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,