How do i delete data from a main xl list using another xl list???

J

jennifer

i have a main emailing list that i keep in xl and sned out my newsletter to
this list each month . however i have collected my unsubscribe list on
another spreadsheet. How do i now remove the unsubscribes from the main list
without having to go through it manually.

thank you
 
B

Bryan Hessey

Assuming that you want to keep your main list in it's current sequence,
and that columns X Y and Z are unused in both sheets.

If you want to match on more than a single field, in the Unsubs sheet
do, in column X, =A1&B1 (formula drag to end) to give a joined field
and use this to lookup.

In your main sheet, in cell Y1 put

=Row()

In your main sheet, assuming column A is the primary key, in cell Z1
put either of:

=VLookup(A1,UnSubs!A:A,1,False)
=VLookup(A1&B1,UnSubs!X:X,1,False)

as required, and formula drag Y1 and Z1 to the end of your data.

Select columns Y and Z and Copy, then Paste Special = Values back over
themselves.

Then select All data and sort over column Z, and delete all not = #N/A
lines (excluding headers), ie, the matches to your UnSubs list.

Re-sort over column Y back to your original sequence, and delete
columns X Y & Z

Hope this helps

--
 
Top