In this guide 6 sections
Copy every sheet before touching it. Merge duplicate households into one family record, mark lapsed members with a reason and a date, agree one name for each field, then import a small test batch. Fix the source files first, because an import copies mistakes faster than any person could.
Freeze and copy the sheets first
Gather every list that holds a name. That means the Excel roll kept by the secretary, the Google Sheet the treasurer started and the sign-in forms from the last open day. Add the exported WhatsApp group. Put the copies in one folder with the date in each file name. Never clean the originals.
Pick one person to own the cleanup. Two people editing two copies is how a household ends up with three addresses. Tell the committee that nobody adds members to the old sheets until the move is finished.
Note where each file came from and who last touched it. When two sheets disagree, the newer, better kept one usually wins.
- Membership roll and renewal register
- Giving or subscription ledger
- Event sign-in sheets and volunteer lists
- Mailing list and group chat exports
Merge duplicate households with written rules
A household is the unit that matters, not the individual row. Sort by surname, then by postcode or street, and look for rows that share a landline, an email or a house number. Mr and Mrs Fernandes listed twice, once under his phone and once under hers, is the classic case.
Write the merge rules down before you start. Retain the oldest joining date. The head of household stays the first contact. Never delete a record without moving its notes into the survivor.
Flag doubtful pairs instead of guessing. Two people called J. Thomas on the same road might be father and son. Ask a committee member who knows them.
Decide what a lapsed member really is
Most club and church sheets mix four people under one blank cell. Some moved away, some died, some stopped paying and some never got a reminder. Each needs a different status, because each deserves a different phone call.
Give every lapsed row a reason and a date. A family that left the parish three years ago should not receive the Christmas appeal, but a member whose cheque bounced last spring might still want to renew.
The history stays. A lapsed record that carries old pledges and dues can answer a question next year, and deleting it cannot.
- Moved away, with the new town if known
- Deceased, with a note for the family record
- Resigned in writing
- Unpaid dues, with the last paid year
- Unreachable, with the last contact attempt
Agree field names before any import
Three sheets will call the same thing Mobile, Phone No and Cell. Choose one name per field and map every old column to it on paper. The mapping table is the single most useful page in the whole project.
Split what the sheets cram together. A single Name column holding Dr. A. K. Rao and family must become title, first name, surname and relationship. Dates should all be written one way, and addresses should have a separate postcode column.
Decide which fields are required. Fewer is better. A member record that insists on a landline will fill up with the digit nine repeated ten times.
Import a test batch and check it against paper
Load fifty households, not the whole roll. Open ten of them beside the original sheet and compare every field. Look at accents in names, leading zeros on phone numbers and dates that flipped between day-month and month-day.
Fix the rule that caused each error, then run the test again. Only when two clean runs pass should the full list go in.
Keep the old sheets read-only for a season. Print the member count by status and ask the secretary to sign it off as a record of the day you switched.
Member cleanup worksheet
Three blocks share one sheet of paper. The first is a field mapping table with columns for old column name, source file, new field name and required or optional. The second is a household merge log with the two record numbers, the survivor, the reason and who approved it. The last is a lapsed status list with reason, date and follow-up owner.
Open a blank worksheet to printQuestions owners ask
Should we delete lapsed members before the import?
No. Mark them with a status, a reason and a date, and import them as inactive. Old giving and dues history helps with renewals, audits and family questions later.
How do we spot duplicate households quickly?
Sort the combined sheet by surname and then by street, and filter for repeated phone numbers and email addresses. Check the matches by eye, because shared numbers sometimes belong to genuinely separate families.
Who should own the cleanup?
One named person, usually the membership secretary, with a second committee member who knows the families to settle doubtful pairs. Shared ownership of the files causes new duplicates.
How big should the test import be?
Enough to include the awkward cases, such as large families, shared addresses and names with accents. A few dozen households is usually enough to expose the mapping mistakes.