• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Scrubbing a list in excel

M

mikmayer

New Member
Hi all,


I have a list of 2,000 email addresses on sheet 1. On sheet 2, I have a list of email addresses that are invalid. The problem is, some of the invalid email addresses on sheet 2 are in the list on sheet 1. How can I somehow find the invalid entries on sheet 1 and take them out easily? I am looking for a if(entry on sheet 1 = any of the entries on sheet 2, remove or highlight in red, leave alone) but not sure how to do that exactly.


Any help would be greatly appreciated. Thanks, Mike
 
You can do it many ways, the easiest I can find is below.


1. Go to sheet 2, select all the invalid mailids and give them a name as list2

2. Come back to sheet 1 and in the adjacent column write =MATCH(A1,list2,0)

3. Drag it down to fill this for all 2000 email ids

4. Now select this results (should be a bunch of numbers and #N/As)

5. Press F5, Click on Special

6. Chose formulas and uncheck "Errors". See this:

http://img.chandoo.org/playground/select-all-formulas-except-errors.png

7. Click ok

8. This selects all cells except error cells (ie invalid email addresses)

9. Press CTRL - (Ctrl and minus keys)

10. Chose Entire Rows and click ok.


All invalid email ids will be gone!
 
You must log in or register to reply here.
Back
Top