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

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

--

Originally Posted by 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