Compare lists when one column contains multiple entries
 

Hello,

I have several spreadsheets that I am needing to compare email addresses
from a column to a list of emails opened, simple enough, however the list i'm
comparing to contains many cells with multiple email addresses. Is there any
way to have excel look at each entry in a cell to compare with or do I have
to list each address in a different cell. Example:

A B
email1 email2 email3 Email5
email4 email5 Email6
email6 Email2

I need to have a formula that would compare column B to Column A and tell me
when it's found a matching email.

Is this possible?

Thanks.

Compare lists when one column contains multiple entries
 

I'm not sure what order you're comparing, but if you wanted to see if an email
address in B1 is in A1, you could use:

=countif(a1,"*"&b1&"*")0

If you see True, then B1 is contained in A1. False means that it's not.

if you wanted to see if the email address in B1 was anywhere in column A:

=countif(a:a,"*"&b1&"*")0

=====
You may have trouble if you have email addresses like:

(in B1)
.... ... (somewhere in column A)

But the formula could be modified for that, too.

Swilliams wrote:

Hello,

I have several spreadsheets that I am needing to compare email addresses
from a column to a list of emails opened, simple enough, however the list i'm
comparing to contains many cells with multiple email addresses. Is there any
way to have excel look at each entry in a cell to compare with or do I have
to list each address in a different cell. Example:

A B
email1 email2 email3 Email5
email4 email5 Email6
email6 Email2

I need to have a formula that would compare column B to Column A and tell me
when it's found a matching email.

Is this possible?

Thanks.

--

Dave Peterson

Compare lists when one column contains multiple entries
 

Maybe this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A$2:A$4)))),"match","no match")

Copied down as needed.

--
Biff
Microsoft Excel MVP


"Swilliams" wrote in message
...
Hello,

I have several spreadsheets that I am needing to compare email addresses
from a column to a list of emails opened, simple enough, however the list
i'm
comparing to contains many cells with multiple email addresses. Is there
any
way to have excel look at each entry in a cell to compare with or do I
have
to list each address in a different cell. Example:

A B
email1 email2 email3 Email5
email4 email5 Email6
email6 Email2

I need to have a formula that would compare column B to Column A and tell
me
when it's found a matching email.

Is this possible?

Thanks.

Compare lists when one column contains multiple entries
 

Looks like this does what I needed Dave, thank you!

"Dave Peterson" wrote:

I'm not sure what order you're comparing, but if you wanted to see if an email
address in B1 is in A1, you could use:

=countif(a1,"*"&b1&"*")0

If you see True, then B1 is contained in A1. False means that it's not.

if you wanted to see if the email address in B1 was anywhere in column A:

=countif(a:a,"*"&b1&"*")0

=====
You may have trouble if you have email addresses like:

(in B1)
.... ... (somewhere in column A)

But the formula could be modified for that, too.

Swilliams wrote:

Hello,

I have several spreadsheets that I am needing to compare email addresses
from a column to a list of emails opened, simple enough, however the list i'm
comparing to contains many cells with multiple email addresses. Is there any
way to have excel look at each entry in a cell to compare with or do I have
to list each address in a different cell. Example:

A B
email1 email2 email3 Email5
email4 email5 Email6
email6 Email2

I need to have a formula that would compare column B to Column A and tell me
when it's found a matching email.

Is this possible?

Thanks.

--

Dave Peterson