THE BRIEF
We have three donor lists: one from our old database, one from the gala, and one from the online donation form. Lots of overlap. Merge them into one list with no duplicates.
Scenario: Development coordinator at a youth arts nonprofit
Package: Merge 3 lists, remove duplicates · $25 · 2 hrs
Done in 40 minutes. 312 duplicates gone.
THE NUMBERS
| List | Rows in |
|---|---|
| Old database | 2,184 |
| Gala signups | 611 |
| Online form | 1,029 |
| Total | 3,824 |
| Duplicates removed | 312 |
| Final list | 3,512 |
- List
- Old database
- Rows in
- 2,184
- List
- Gala signups
- Rows in
- 611
- List
- Online form
- Rows in
- 1,029
- List
- Total
- Rows in
- 3,824
- List
- Duplicates removed
- Rows in
- 312
- List
- Final list
- Rows in
- 3,512
THE RULE
Match on email first. No email? Match on first name + last name + ZIP. Lowercased everything and trimmed spaces before matching. "Ana.Lopez@Gmail.com " and "ana.lopez@gmail.com" are the same person. Obviously.
WHEN TWO ROWS DISAGREED
- Kept the newest address (online form beats gala beats old database).
- Kept the phone number that wasn't blank.
- Kept BOTH donation dates in separate columns. Never throw away giving history.
SPOT-CHECK
Pulled 20 random merges and checked them by hand. 19 right. 1 was a mother and daughter with the same name at the same address. Split them back out and added a rule: different birth year in the gala sheet = different person. Re-ran. Found 2 more like that. Fixed.
THE FILE
- Tab "Clean list": 3,512 rows, one per donor.
- Tab "Merged rows": all 312 merges, with which rows became which. So you can undo anything.
- Tab "Check these": 14 rows where names matched but emails didn't. Probably the same person. Your call.
- No merged cells. Ever.
TIME
Import: 6 min. Matching rule: 12 min. Spot-check and fix: 15 min. Tabs and notes: 7 min.
Ctrl+S. — Elena
