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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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

--
 
Back
Top