Excel Question

Jason - Nov 3, 2010 at 06:35 PM
 Gio - Nov 4, 2010 at 11:56 AM

I have one worksheet with with all of my emails (both good and bad) D2:D3843 and another worksheet A2:A1061 with all good emails and I need to remove all of the bad emails from the first worksheet.... Any Ideas?

1 reply

Do a vlookup on the good worksheet emails, mark an extra column that says "good" next to each of those items. On the good/bad worksheet, where there is a match, after sorting, you'll know the "bad emails" because it will have a result like "#N\A" as a result from not being on the good email list.
Thank you!! I appreciate it but it didn't work... Can you talk to me like I'm 8 I'm just not getting it... i.e. where do I put the formula, etc?
You have to know how to use vlookup first. Its a built in function in excel. I take it you dont know how to use vlookup, hence you are asking me.

On your excel worksheet that has the "good" emails, create an extra column. For simplicity, you'll have two columns, column A will have your "good" emails, and column B will have the value "good email" for all your records in column B. Highlight all the records for that worksheet, then on the Menu Toolbar go to INSERT > NAME > DEFINE. You will define this range of selection, and name it something meaningful. I used goodemail as my name. Press OK to add to the defined list.

On your "good/bad" worksheet, for simplicity, you will have two columns, again column A will have all emails of good/bad, and place your formula on column B. Column B will have formula as follows =vlookup(A2,goodemail,2,false). A2 indicates the cell you are trying to reference, goodemail is the reference location, 2 is the column you need to reference, and false indicates it needs an exact match. Feel free to further google how to use vlookup if my explanation is a bit vague.

Records that show up with the value "good email" are just that, your good emails, and values that indicate "#N/A", means it could not find that record, hence its your bad emails.