If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 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 |
#2
|
|||
|
|||
finding 600 emails in a column
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","N o") 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" wrote in message ... 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 |
#3
|
|||
|
|||
finding 600 emails in a column
If for some reason, this didn't work ..
Put in B1: =IF(ISNUMBER(MATCH(A1,Established!A:A,0)),"Yes","N o") 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))*(Esta blished!$ 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 -- |
#4
|
|||
|
|||
finding 600 emails in a column
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 -- |
#5
|
|||
|
|||
finding 600 emails in a column
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 -- |
#7
|
|||
|
|||
finding 600 emails in a column
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","N o") 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" wrote in message ... 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 |
#8
|
|||
|
|||
finding 600 emails in a column
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" wrote in message ... bRILLIANT! I WILL TRY THIS AS SOON AS I GET A CHANCE. THANK YOU, MAX! |
#9
|
|||
|
|||
finding 600 emails in a column
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" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
creating a bar graph | Johnfli | General Discussion | 0 | October 26th, 2005 08:16 PM |
match and count words | David | Worksheet Functions | 5 | July 4th, 2005 02:24 AM |
Return Count for LAST NonBlank Cell in each Row | Sam via OfficeKB.com | Worksheet Functions | 12 | April 17th, 2005 10:36 PM |
Finding specific word in column | Phil #3 | Worksheet Functions | 3 | March 28th, 2005 09:00 AM |
How can I sort an entire spreadsheet from a list | prod sorter | Worksheet Functions | 4 | November 17th, 2004 03:43 AM |