Here's the scenario: we wanted to send an email to a group of about 400 supporters. Due to a poor import user experience on the part of MailChimp (more on that later), the email only went to about ¾ of the list. So now we need to send the same email out to a new list containing only the people who didn't receive the first one. Basically we want to subtract one list from the other:
Original list (400 people) - Sent list (300 people) = Remainder list (100 people)
I used a modified version of this de-duping process.
- Open Excel
- Paste in the original list
- Append the sent list onto the bottom of the original
- Sort A-Z
- Click in cell B2, then paste in this formula:
- Grab the drag handle in the lower-right corner of the cell, and drag it down to fill in the formula for all of the cells below.
- Clcik on the B heading to select the whole B row
- New spreadsheet
- Edit > Paste Special
- Choose values
- Click OK
- Click A1 to select the whole row
- Sort Z-A (this will put the empty cells on the bottom)
- Save as CSV
What the formula does is check the cell in column A's previous and next neighbors. If either match the current cell, it leaves the cell in column B blank. Otherwise it copies the address over from column A to column B. You are left with only the unique addresses, which are the ones that have not yet been sent to.