finding 600 emails in a column

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

--
farrell
0
12/27/2005 6:55:01 PM
excel.newusers 15348 articles. 2 followers. Follow

8 Replies
291 Views

Similar Articles

[PageSpeed] 23

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" <farrell.20q5e7@news.excelbanter.com> wrote in message
news:farrell.20q5e7@news.excelbanter.com...
>
> 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


0
demechanik (4694)
12/28/2005 1:54:12 AM
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
--


0
demechanik (4694)
12/28/2005 2:10:37 AM
> 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
--


0
demechanik (4694)
12/28/2005 2:26:42 AM
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
--


0
demechanik (4694)
12/28/2005 9:59:04 AM
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" farrell.20q5e7@news.excelbanter.com wrote in message
> news:farrell.20q5e7@news.excelbanter.com...
> 
> 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
0
12/28/2005 5:29:24 PM
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 Wrote:
> One try ..
> 
> Assuming the data for both POTENTIAL CUSTOMER and ESTABLISHED CUSTOMER
> 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" farrell.20q5e7@news.excelbanter.com wrote in message
> news:farrell.20q5e7@news.excelbanter.com...
> 
> 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 a
> 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 i
> one
> column.
> 
> But i don't know what to do next.  I have 600 POTENTIAL CUSTOME
> emails
> that i need to check against the established list.  The only way 
> 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?
> 
> 
> --
> farrel

--
farrell
0
12/28/2005 10:33:28 PM
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" <farrell.20rje7@news.excelbanter.com> wrote in message
news:farrell.20rje7@news.excelbanter.com...
>
> bRILLIANT!  I WILL TRY THIS AS SOON AS I GET A CHANCE.
>
> THANK YOU, MAX!


0
demechanik (4694)
12/28/2005 10:55:28 PM
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" <farrell.20s028@news.excelbanter.com> wrote in message
news:farrell.20s028@news.excelbanter.com...
>
> 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


0
demechanik (4694)
12/29/2005 1:06:09 AM
Reply:

Similar Artilces:

Advanced Find
Hi All, Does anyone know how the advanced find utility can be used to create "OR" sql queries? Currently, issuing multiple criteria in V1.2 results in an "AND" query only. TIA... Hi! The only possibility for you is to seperate the values with ";" for example: field1 = "a" or field1 ="b" "a;b" but you can not search for field1 = "a" or field2 = "b" -- Christian support@mscrm-addons.com www.mscrm-addons.com Your company for MS-CRM ADD-ONS! GroupCalendar for MSCRM Related Documents for MS...

Allow or Deny when a Program tries to send an email
I am trying to send a message through Outlook with a vb.net program. I receive a message saying that I can allow or deny this message. After a few of these messages, I started getting a different message that gave me a choice to ALWAYS allow messages to be sent from this program. I don't know what triggered the appearance of this send message. My problem is that I need to install my application on my user machines, does anyone know how to disable this allow or deny message OR trigger the send message which allows me select always allow? Thanks, What version of Out...

Saving existing Outlook emails
I'm needing to re-format my harddrive and am wondering how I can save my existing Outlook emails and settings so that once I have the newly reformatted drive setup I can access my saved emails and files? do a search in your hard drive for *.pst... it contains all e-mails and contacts... in brief, all eggs in one basket. If you have mail rules you can export them, as you can your mail account(s). HTH <anonymous@discussions.microsoft.com> wrote in message news:10fe01c4a73d$16a62f50$a301280a@phx.gbl... > I'm needing to re-format my harddrive and am wondering how > I can ...

sum of a column according to two or more variables
I have a master log with a column called hours lost, a column calle vendor, one called problem type and the rows are labeled and sorted b date. I would like to sum the hours lost column for each month according t the month and vendor, and have the sum end up in one cell I would also like to sum the hours lost column for each month accordin to the month and problem type and have the sum end up in one cell basically I only want the hours lost data for a specific vendor an month at one time or a specific problem type and month at one time, bu I don't know how to set up the formula correctl...

Workflow rule on (Order)Products and columns of related entities in advanced find view
Hi, Does anyone know whether it's possible to create workflow rules on (Order)Products, since the entity Products isn't part of the standard workflow entity? In my example I have added a new (expiry) date attribute on the OrderProduct form. Now I would like to add a workflow rule on that datefield to create a task when the expiry date is nearly reached; but the problem I have is that i can't "reach" the fields on the OrderProduct form to put a workflow rule on? Another problem I have is that I've created an advanced find query in which I query customers who have or...

Convert Column to row with variable data
Hi I'm using Excel 2k and I have a spreadsheet that looks like this Name Address Fred 21 Blah St London Sue Tower 50 London EC2 and need it to look like this Name Address 1 Address2 Address3 Fred 21 Blah St London Sue Tower 50 London EC2 I have a macro that can convert from column to row but only for a set number of columns. Is there any way to account for the variable amount of data for each address ? Any help much appreciated Thanks David David: I suggest the following formulae - copied down as necessary: C2: =IF(...

make XL stop interpreting email addresses as highlighted links?
In several worksheets I store email addresses but XL insists on making them links. So, if I ever select the cell, it tries to open Outlook (which I won't let on my machine), etc. It takes me 30 seconds to get control back. I've looked everywhere in Options but I haven't found anything to stop this. My workaround is to type the email address in an unused cell, then copy it and Paste Special into the target cell with "values only". What a pain. Can you gurus please help? By the way, I did look hard and long for this in earlier posts -- I can't be the only one...

Adding attachments to email merges
I am trying to create an email merge in Outlook, with a PDF attachment. It all works ok, and the message sends but the attaachment is always stripped off the message - I have tried different files and the attachement never comes through, or even gets mentioned in the received message. What am I doing wrong? Any suggestions gratefully received. Many thanks m.nutt1 Not supported. You'd need to use third party software for that. http://www.slipstick.com/addins/mail.htm#massmail -- Russ Valentine [MVP-Outlook] "m.nutt1" <mark@emqc.co.uk> wrote in message news:%23qnq...

want to add all $ in column c where column A is the same
I'm very new at Excel, and a real math dummy. I've figured out how to enter a formula when all the $ I want to add are together, but I can't figure out how to do that when I want to select only the $ values for certain items listed throughout the spreadsheet. For example: I keep a running list of Architects, their projects and $ values of each project as they are assigned. I want to automatically calculate the total current $ value for each Architect without having to sort them in order, or create a separate table for each architect. Can I do that? Here's what th...

reflecting values in a column into a row
I am creating a chart to map a round-robin chess game. If there are 4 players, then all 4 has to play one another. if I have the names John Mike Sally Bill Then I'd like to type them into a columns and write a formula in a row to pick up the names the spreadsheet should then look like this: John Mike Sally Bill John Mike Sally Bill I think it may be achieved with the Indirect() function, but my Excel 2007 help seems broken and I can't figure it out without an example. Thanks. MikeB With names in A2:A5 Enter in B1 =INDIRECT("A"&COLUMN(B1)) Or...

Remove last letter from column
Hi, I have a list of titles and some titles have a letter A or B at the end.. is there a function/formula I can use to remove them if it ends in A or B? For example (my list): Accounting Sr Mgr B Accounts Payable Sr Mgr B Ambulatory Plng Sr Prog Dir A Need it to look like this: Accounting Sr Mgr Accounts Payable Sr Mgr Ambulatory Plng Sr Prog Dir Thanks! This will get rid of the A or B at the end along with the space before it. Assuming the value is in A5: =IF(OR(RIGHT(A5,2)=" B",RIGHT(A5,2)=" A"),LEFT(A5,LEN(A5)-2),A5) kvc wrote: > Hi, I have a list of titles a...

Workflow Email on Campaign Response
I've added a workflow rule so that when a field is changed on the campaign reponse form a email will be sent but when i go to add the action "send email" is not listed. Is it not possible to do this? This is CRM 3.0 Thanks I am facing the same problem. In CRM 3.0, when I want to add a workflow rule On creating a campaign response, "Send email" is not an available option in the Insert Action menue. Does anyone know the solution? -- Raj Limaye, PMP "Matt" wrote: > I've added a workflow rule so that when a field is changed on the campaign &...

Empty Emails
I get daily emails that are empty. Blank. From: Blank To: Blank Body: Blank Can someone tell my why? I just delete them. Thanks Sue Nope, I do too, but SpamBayes takes care of them for me. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the (insert latest virus name here) virus, all mail sent to my personal account will be deleted without reading. After furious head scratching, Sue asked: | I get daily emails that are empty. Blank. | From: Blank | To: Blank | Body: Blank | | Can someone tell my why? I just delete them. | Thanks | ...

linking to publisher email
How do you make a link for unsubscribing to an email? The easiest way is to insert a email link to you along with instructions. Select the text or image > insert > hyperlink > Email Address and enter your email address. DavidF "NK" <NK@discussions.microsoft.com> wrote in message news:166F126E-17FC-468E-9A42-9E23ACE8454D@microsoft.com... > How do you make a link for unsubscribing to an email? ...

Problem OL 2002 Email
I am having a problem with OL 2002 email settings. I have two computers which I am setting up. One computer works perfectly. The second computer I have configured exactly the same...incoming/outgoing servers and ports, etc... are all setup the same. On the second computer when I press test email settings, everything tests fine. But when I try to do a Send/Receive, I run into problems. First, the POP3 tries to connect to the POP3 server twice at the same time. (I am able to see this on my POP3 server console.) Second, the SMTP sending mail never connects to my SMTP server. (Ag...

Change Row/Column Height & Width
I know I should be able to automatically set a row height to the max necessary by hovering the cursor between the 2 rows I want to adjust and double-clicking, but sometimes this doesn't always work. Why is that that - do I need to adjust a setting? And is there any way to set it so that if text is added or deleted the row height would change automatically so thatthe text fit appropriately? Set the row format to Autofit and cells to Wrap Text Gord Dibben MS Excel MVP On Tue, 19 May 2009 12:14:04 -0700, DaveL <DaveL@discussions.microsoft.com> wrote: >I know I should be ...

view source of email?
Hi - does anyone know how to view the source code of an email in Outlook 2003? - tia Jam4 ...

Emails don't find contacts in restored Outlook
My hard drive burned up, so I replaced it, reinstalled, and imported everything into Outlook. The contacts are there if you click contacts. However, when sending emails, there is no way to connect to my contacts. It's like they are invisible. You never import Outlook data to restore it. You just open the Outlook data file. When you do, you will be able to configure your address book view to display your Contacts the same way as always: http://support.microsoft.com/default.aspx?scid=kb;en-us;287563&Product=ol2002 -- Russ Valentine "Mike Row Soft" <MikeR...

Why can't I send email messages with Trial Outlook 2007?
I have a new HP laptop that came with the 60 day trial of Office'07. I receive email but the send/reply/compose functions are grayed out. I am certain my settings are correct. Why can't I send email? KK What happens when you use the Test Account Settings? What is the exact error message you get? -- Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. ALWAYS post your Outlook version. How to ask a question: http://support.microsoft.com/KB/555375 After furious head scratching, KK asked: | I have a new HP laptop that came with the 60 day...

Sending email in CRM web app problem
Hi, I found this problem very annoying. Our CRM's SMTP service is hosted in YahooMail. The SMTP address is mail.bizmail.yahoo.com and this is a "locked down" server, which means required authentication by username and password. I configured the CRM server's registry by settings the SMTPServerUrl and other stuff, such as Username and password entries. Now, when we send emails from CRM web app, it works... BUT... when we send email using a template which has embedded images, it cannot send the email and reports an error: "Not valid association" something... A...

change column name from letters to numbers?
I'm importing a 3rd party CSV file into Excel and then comparing some of the contents to a printed spec. Each numbered field in the spec corresponds to a column in the work sheet. I'd like to change the column headers from letters to numbers. Is that possible? Tools-->Options, General tab. Check the R1C1 Reference Style. ******************* ~Anne Troy www.OfficeArticles.com www.MyExpertsOnline.com "Jerry" <jerry@nowhere.com> wrote in message news:OoyPef$eFHA.3048@TK2MSFTNGP12.phx.gbl... > I'm importing a 3rd party CSV file into Excel and then comparing ...

Incoming Emails #2
Do I always have to click the "Send/Receive" button in order to get my new emails? No, you can go to TOOLS, then SEND/RECEIVE SETTINGS and then DEFINE SEND/RECEIVE SETTINGS and in the box's shown, you can tell Outlook to automatically send or receive your messages every XX mins. Tedd -- Tedd Riggs PDASquare Content Developer www.pdasquire.com "wj" <anonymous@discussions.microsoft.com> wrote in message news:88174331-72B4-4ECF-B688-DF745D9178EF@microsoft.com... > Do I always have to click the "Send/Receive" button in order to get my new emails? T...

column value translation
I'm sorry if this is already here somewhere, but I could't find any references. I need to upload a list of people into our computer system and this list is comprised of their names and the code for the branch where they work. The computer system into which I need to upload this list will not recognize the current branch ID code for those employees, but I do have a list that is basically a comparison of the two different codes. For example branch code 800 on the list equals branch code C001 in the system. I need to get a way in excel to convert all the branch codes that are next...

number of results columns doesnt match table defintion
This is the error I get when among other things, I try to print a financial report. Actually the error popup says "A get/change operation on table 'GL_Options_ROPT' failed accessing SQL data", the more button reveals the number of columns error description. This database was restored by copying the sql folder from a previous installation into the new servers sql folder. Thanks. shawn modersohn wrote: > This is the error I get when among other things, I try to print a > financial report. Actually the error popup says "A get/change operation > on ta...

Don't get some emails;
Some emails don't come thru. From another computer I can forward messages to my email site but don't receive new messages. Also not getting some emails from various sites. ...