MrExcel Publishing
Your One Stop for Excel Tips & Solutions

To remove data from column


Posted by Michael C on September 15, 2001 2:23 PM

I have a list of e-mail address to remove from file.
The originial excel file contained a column of e-mail address; my question is: how I can set the app to lookup the list of removed email address and remove from the list?


Posted by Eric on September 17, 2001 6:48 AM

a possible vlookup solution

Say your "original" list is in A2:A?? and your "to be deleted" list is in B2:B100. In C2 enter the formula:
=vlookup(a2,b$2:b$100,1,false)
, and copy down as needed. which will report the email address in col (A) if it was also found in col (B), but will report
#N/A
for emails in Col (A) that are not also in Col (B)
So in this case you would want to keep only those emails from Col (A) for which the corresponding value in col (C) was #N/A
so you could autofilter to keep #N/A's or you could sort on column c.
Hope that helps