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,

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 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 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,

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,

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,

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. ) 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" wrote:

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,

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


--
Regards,

Peo Sjoblom


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

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,

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,

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.


"Marilyn" wrote:

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. ) 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" wrote:

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,

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,



"JMB" wrote:

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.


"Marilyn" wrote:

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. ) 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" wrote:

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,