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 engines
large and small.

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

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

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

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

I know there's a better way!!!

Can anyone help?


--
farrell

[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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"farrell" <[email protected]> wrote in message
news:[email protected]...
>
> Hi! Thank you for taking the time to read this.
>
> I run an internet retail business. We advertise on search engines
> large and small.
>
> I have to make a decision on whether or not one of our smaller ad sites
> is paying off. Basically, the ad site supplies us with a list of
> POTENTIAL CUSTOMER emails. We send out monthly promotional emails to
> them.
>
> Now i need to track these emails to see if any of them have become
> ESTABLISHED CUSTOMERS.
>
> So far, I fed information from our main data base of ESTABLISHED
> CUSTOMERS into EXCELL so all the ESTABLISHED CUSTOMER emails are in one
> column.
>
> But i don't know what to do next. I have 600 POTENTIAL CUSTOMER emails
> that i need to check against the established list. The only way i can
> think of , with my limited Excell knowledge, is searching using the
> edit>find function one by one until i'm done with the list of 600
> emails.
>
> I know there's a better way!!!
>
> Can anyone help?
>
>
> --
> farrell

[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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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>)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[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
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

[farrell]

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

THANK YOU, MAX!


Max Wrote:
> 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.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "farrell" [email protected] wrote in message
> news:[email protected]...
>
> Hi! Thank you for taking the time to read this.
>
> I run an internet retail business. We advertise on search engines
> large and small.
>
> I have to make a decision on whether or not one of our smaller ad
> sites
> is paying off. Basically, the ad site supplies us with a list of
> POTENTIAL CUSTOMER emails. We send out monthly promotional emails to
> them.
>
> Now i need to track these emails to see if any of them have become
> ESTABLISHED CUSTOMERS.
>
> So far, I fed information from our main data base of ESTABLISHED
> CUSTOMERS into EXCELL so all the ESTABLISHED CUSTOMER emails are in
> one
> column.
>
> But i don't know what to do next. I have 600 POTENTIAL CUSTOMER
> emails
> that i need to check against the established list. The only way i
> can
> think of , with my limited Excell knowledge, is searching using the
> editfind function one by one until i'm done with the list of 600
> emails.
>
> I know there's a better way!!!
>
> Can anyone help?
>
>
> --
> farrell


--
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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"farrell" <[email protected]> wrote in message
news:[email protected]...
>
> 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 of
POTENTIAL customers have become ESTABLISHED ones.

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

Farrell



Max Wrote:
> 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.
> --
> Rgds
> Max
> xl 97
> ---
> Singapore, GMT+8
> xdemechanik
> http://savefile.com/projects/236895
> --
> "farrell" [email protected] wrote in message
> news:[email protected]...
>
> Hi! Thank you for taking the time to read this.
>
> I run an internet retail business. We advertise on search engines
> large and small.
>
> I have to make a decision on whether or not one of our smaller ad
> sites
> is paying off. Basically, the ad site supplies us with a list of
> POTENTIAL CUSTOMER emails. We send out monthly promotional emails to
> them.
>
> Now i need to track these emails to see if any of them have become
> ESTABLISHED CUSTOMERS.
>
> So far, I fed information from our main data base of ESTABLISHED
> CUSTOMERS into EXCELL so all the ESTABLISHED CUSTOMER emails are in
> one
> column.
>
> But i don't know what to do next. I have 600 POTENTIAL CUSTOMER
> emails
> that i need to check against the established list. The only way i
> can
> think of , with my limited Excell knowledge, is searching using the
> editfind function one by one until i'm done with the list of 600
> emails.
>
> I know there's a better way!!!
>
> Can anyone help?
>
>
> --
> farrell


--
farrell

[Max]

Always great to hear that it worked !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"farrell" <[email protected]> wrote in message
news:[email protected]...
>
> Oh my, Max. You are genius!!! It worked, it worked! If you care,
> we've decided to keep this advertiser because a fair percentage of
> POTENTIAL customers have become ESTABLISHED ones.
>
> (i'm amazed that you even understood my mumbo jumbled question)
>
> Farrell