asked on

Compare two columns in Excel for string comparisons

Hi i have a list of email domains in a column in excel. I want to bounce the domains up against a list of folks who sign up and write an indicator to a field.

So Sheet1 has all my Domains in column A. They will look like (without the @):

yahoo.com
gmail.com

Sheet2 has the new sign ups in column A. They will be full email addresses:

mister.you@yahoo.com
you.mister@gmail.com

Every time a new applicant signs up, i want to put a YES or NO in sheet2 Column B when there is a match.

So,

mister.you@yahoo.com YES
mister.you@phone.com NO

Can anyone help?

Thank you

[ fanpages ]

In [Sheet2]:

Set the formula in cell [B1] to:
=IF(ISERROR(MATCH(MID(A1,FIND("@",A1)+1,255),Sheet1!A:A,0)),"NO","YES")

Copy cell [B1] to the clipboard & copy down column [ B ] as far as data extends in column [A].

farmingtonis

ASKER

Hi fan, sorry, i need to do this in vba. sorry, i should have put that in the description

Subodh Tiwari (Neeraj)

Assuming your domains on Sheet1 are in A1:A2 and users signups on Sheet2 start from A2, then in B2 try this Array Formula which requires confirmation with Ctrl+Shift+Enter instead of Enter alone.
In B2

=IF(OR(ISNUMBER(SEARCH(Sheet1!$A$1:$A$2,A2))),"Yes","No")

Open in new window

and then copy down.

[ fanpages ]

So, do you want the formula in every cell in column [ B ], or just the explicit value "NO", or "YES"?

PS. For anybody viewing this thread in the future, an example using my first (formula-based) solution is attached.
Q_28903591.xlsx

Professor J

use this

=IF(A1<>"",IF(ISNUMBER(MATCH(RIGHT(A1,LEN(A1)-SEARCH("@",A1)),Sheet1!A:A,0)),"Yes","No"),"")

plz see attached.
Book1.xlsx

[ fanpages ]

Guys... please read ID: 41381690.

Professor J

fanpages,

thanks. seems like all we did was in vain .

i do not understand when a task can be done with bulit-in function, why to go for VBA.

anyways, fanpages, i will leave this for you.

ASKER CERTIFIED SOLUTION

Subodh Tiwari (Neeraj)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

farmingtonis

ASKER

It has to be vba since these are automated tasks.

farmingtonis

ASKER

worked perfectly

Subodh Tiwari (Neeraj)

Glad to know that.

Professor J

well done sktneer

Subodh Tiwari (Neeraj)

Thanks Professor for the appreciation. Much appreciated.

[ fanpages ]

Equally, as I was attempting to seek clarification upon above, you could always set (any of) the formulae supplied (by any of the contributing "Experts") within the cells in column [ B ] via Visual Basic for Applications code.

Then you would not have explicit values of "NO", or "YES"; the outcome would still be 'dynamic' if any of the contents of [Sheet1] (or [Sheet2]) column [A] changed.

This aside, the question has been answered.