finding 600 emails in a column

[farrell]

Hi! Thank you for taking the time to read this.

I run an internet retail business. We advertise on search engine
large and small.

I have to make a decision on whether or not one of our smaller ad site
is paying off. Basically, the ad site supplies us with a list o
POTENTIAL CUSTOMER emails. We send out monthly promotional emails t
them.

Now i need to track these emails to see if any of them have becom
ESTABLISHED CUSTOMERS.

So far, I fed information from our main data base of ESTABLISHE
CUSTOMERS into EXCELL so all the ESTABLISHED CUSTOMER emails are in on
column.

But i don't know what to do next. I have 600 POTENTIAL CUSTOMER email
that i need to check against the established list. The only way i ca
think of , with my limited Excell knowledge, is searching using th
edit>find function one by one until i'm done with the list of 60
emails.

I know there's a better way!!!

Can anyone help

 

[Max]

One try ..

Assuming the data for both POTENTIAL CUSTOMER and ESTABLISHED CUSTOMERS are
listed in col A, in A1 down in sheets named as:
Potenial, Established

In sheet: Potenial

Put in B1:
=IF(ISNUMBER(MATCH(A1,Established!A:A,0)),"Yes","No")
Copy down as far as required (to B600?)

Col B will return a "Yes" next to any cust which is found
in the sheet: Established, "No" otherwise.

 

[Max]

If for some reason, this didn't work ..

Put in B1:
=IF(ISNUMBER(MATCH(A1,Established!A:A,0)),"Yes","No")
Copy down as far as required (to B600?)

try this "heavier-duty" alternative instead:

Put in B1:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH(Established!$A$1:$A$100,A1))*(Established!$
A$1:$A$100<>""))>0,"Yes","No")
Copy down as far as required

Adapt the range: Established!$A$1:$A$100 to suit. Use the smallest range
sufficient to cover the list in "Established", but note that we can't use
entire col references, eg: Established!A:A, in SUMPRODUCT.

 

[Max]

Potenial, Established

In sheet: Potenial

Oops, "Potenial" should read as: Potential in the above lines
(Guess my "T" key wasn't working too well <g>)

 

[Max]

From where you're posting/reading this, Excelbanter??,
one observation is that Excelbanter seems to remove some operator symbols,
eg: "greater than", "less than" etc, even those from within posted formulas.
This would cause problems in trying out the formulas posted, eg, when you do
direct copy of formulas from reply posted and paste into cells, as these
symbols, if present in the formulas, would have been quietly removed <g>.

Perhaps you might want to read this thread in google instead:
http://tinyurl.com/c7v3e

And for easy reference,
here's a sample implementation of the 2 options:
http://cjoint.com/?mCkTFXh68u
farrel_newusers.xls

 

[farrell]

bRILLIANT! I WILL TRY THIS AS SOON AS I GET A CHANCE.

THANK YOU, MAX!

 

[farrell]

Oh my, Max. You are genius!!! It worked, it worked! If you care
we've decided to keep this advertiser because a fair percentage o
POTENTIAL customers have become ESTABLISHED ones.

(i'm amazed that you even understood my mumbo jumbled question)

Farrell

 

[Max]

You're welcome !

Pl note comments in my other response on the removal of eg: "greater than",
"less than", "not equal to" operator symbols by Excelbanter. This comment
would apply for the *2nd* "back-up" formula suggested. I've also provided a
google link to this thread and a link to a sample file there.

 

[Max]

Always great to hear that it worked !
Thanks for the feedback ..