[SOLVED] Sort/Remove Script?

rosscoloco · #1 07-01-2009, 02:29 PM

Hey guys, I'm from a Laptop Repair Company. Sorry to barge in here and ask for help randomly, but any suggestions would be greatly greatly appreciated. I post mostly on Actionscript forums to help others using AS3.0, so if you have a question there I could definitely help you lol

I have 2 .xls documents with customer contact information. One has a list of old contacts I need to remove which is about 3k this list contains only email addresses. The other contains the full list of Customers this list is about 11k this contains everything from first/last name to billing and email addresses.

My goal is to double check to see which addresses are just typos and which are non-working.

I will need to go through the list line by line but better to only go through 3000 than 11000. So, I need to somehow remove all of the good addresses from the full list using the bad address list which is only 3k long.

Any suggestions on where to start?

~Rossco

rosscoloco · #2 07-02-2009, 02:40 PM

Still kind of banging my head against the wall here!

protonLeah · #3 07-02-2009, 11:57 PM

Post a sample workbook(s) that shows the exact layout of your data; but with sensitive info dummied out. First thoughts are that you can copy/paste the short list into an unused area in the long list and use Conditional Formatting to highlight those values in the long list that match (hint) values in the short list.

rosscoloco · #4 07-03-2009, 04:01 PM

Here is a sample of what I have.

Customers1.xls

There are 2 workbooks in the file. One contains the customer's information, the other is the list of emails that are not valid.

I do emails to our past customers who subscribe to our newsletter, and I got 3k bounces, so I want to go through those, check to see if it is a simple typo, or remove them so I don't get 3k bounces - but I don't want to simply remove 3k customers from my list.

I'd like to make sure you don't think I am a spammer - these are for subscribed customers who WANT to get our emails.

This highlighting method sounds like a great idea. That would help me soooooo much.

I'm really unsure how to write that script, though. Could you perhaps point me in the right direction? I've programmed before, but am unfamiliar with Excel syntax(although catch on quickly to new languages).

protonLeah · #5 07-03-2009, 04:37 PM

Customers1.xls >> 404 URL NOT FOUND!

Please post your workbook directly to the forum (use the paperclip icon from the toolbar). If it is more than 1MB in size (it shouldn't be that large), you will have to zip it first.

rosscoloco · #6 07-03-2009, 05:10 PM

Sorry, I had a lower case C in the link name..... I didn't see the attach link my bad

Here it is:Customers1.xls

Here is the same but on my website: Customers1.xls

martindwilson · #7 07-03-2009, 06:31 PM

on your customer list
in a a spare column starting in row 2
=ISNUMBER(MATCH(L2,ToCheck!$A$2:$A$3000,0))
and drag down
any match found will return true adjust A$2:$A$3000, to fit the range on your real list
you can then autofilter on true and delete visible rows

rosscoloco · #8 07-03-2009, 07:22 PM

Customers1.xls

I did what you ask, but didn't get the results - it's saying FALSE even though the statement is true...... I must be missing something here.

is

=ISNUMBER(MATCH(L2,ToCheck!$P$2:$P$3000,0))

not right?

rosscoloco · #9 07-03-2009, 07:45 PM

My bad, I figured it out. It's not searching through the List, it's looking through the file or worksheet.

I got it all working, and THANK YOU ALLL!!!!!

rosscoloco · #10 07-03-2009, 07:48 PM

So, for anyone who searched this problem and finds it later,

=ISNUMBER(MATCH(L2,ToCheck!$A$2:$A$3000,0))

If your excel file only has 1 workbook, it will ask you for another xls file when you paste these in. Have a separate .xls file and then it will go through it. ToCheck! apparently goes to another database - it will by default use another workbook in the .xls, but if there is only 1 workbook, it will prompt you to find another file.

Works like a charm - saved myself looking through 2000 records switching screen by screen.........thank god!

I LOVE U GUYS!

SOLVED!

JBeaucaire · #11 07-03-2009, 10:12 PM

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

rosscoloco · #12 07-05-2009, 12:33 AM

Done! SOLVED!

Welcome to the Excel Forum

Forum Statistics