How to Choose Which Record to Keep When Duplicates Disagree
Finding duplicates is the easy half. The hard half comes next: the two rows for one person do not agree. One has a new phone and an old street. The other has the new street and no phone. One says she opted in. The other says she opted out. Which record do you keep — and which facts win when the rows clash?
There is no single right row. There is a right method: merge field by field, with a set order of trust, so the survivor is built from the best fact in each column. This guide gives you that order. It works by hand for a few rows and inside a tool for a few thousand.
Two warnings frame the whole topic. First, never let an empty field beat a full one. Second, when two full fields clash and you cannot tell which is right, the tie goes to safety: for opt-outs, the “out” wins; for most other facts, the newer one wins — and the loser is saved in your audit file, not erased. More on that file in the audit file guide.
You do not need to make these calls one by one for the whole list. Most groups are clear: one row is full and new, the rest are thin and old. The rules in this guide sort those groups fast. What is left is a small pile of true clashes. Those are the ones worth your time. Rules for the many, eyes for the few — that is the shape of a sane cleanup.
The order of trust, field by field
Use the same order each time, and write it down for your team. Start with facts that carry dates: the row with the newest update usually holds the newest truth for contact facts — street, phone, job. Next, trust fullness: a row that is 90% full was likely typed with care or synced from a good source. Last, trust the source: a row the person typed last week beats a row a clerk typed from a paper form two years ago.
| Clash | Default winner | Why |
|---|---|---|
| New street vs. old street | The newer row | People move; new data tracks them |
| Phone vs. blank | The phone | A blank is not a fact |
| Two phones, both look real | The newer one; save the other | Keep both in the audit file |
| Opt-in vs. opt-out | The opt-out | Law and trust beat reach |
| Name spelled two ways | The fuller, formal form | Display names should be complete |
| Two firms for one person | The newer row; note the old | Jobs change; keep the history |
Notice what is not in the order: row position. “Keep the first row” is the default in many tools because it is simple, not because it is right. The first row is often the oldest — the one most likely to be stale. If your tool only offers keep-first, sort your file so the rows you trust most come first in each group, or use a tool with a “most complete” rule. DedupeCSV offers first, last, most complete, and most recent as survivor rules for this reason.
Fill the gaps before you judge the clash
Most pairs do not clash as much as they seem to. They complement: each row holds facts the other lacks. So step one is always gap-filling. Copy each fact the survivor lacks from the other rows in the group. Only after the gaps are filled do true clashes remain — columns where two rows hold two different real values. Those are the only ones that need your judgment.
Gap-filling is where careless dedupe loses data, and careful dedupe gains it. A survivor built from three rows can end up richer than any of them: the email from row one, the phone from row two, the opt-in date from row three. Judged as a whole, the merge did not just remove two rows. It built the best record of that person you have ever had.
Dates are your best witness
When dates exist, use them. “Updated at,” “last order,” “sign-up date” — these stamps tell you which row saw the person most recently. Recent rows hold current streets, current jobs, current phones. But dates can lie in one way: a bulk import can stamp old data with a new date. If every row in a file shares one import date, that date tells you about the import, not the person. Trust dates that came from real events over dates that came from file moves.
Opt-in dates deserve special care. When rows clash on consent, do not just keep the newest flag. Keep the newest consent event with its date and source. If one row says “opted in at the fair, June” and a later row says “opted out, web form, August,” the August out wins and the June in is history. Consent is a story, not a switch — keep the story in the record.
Dates also settle the “which email” clash most of the time. People keep one main inbox at a time. The address on the newest row is the one they read now. The older one may still work — or may land in a dead inbox, or a past job's mail room. Send to the newest. Keep the older in the record as a spare. When the newest one bounces, the spare earns its keep at once.
When you cannot tell, keep both facts
Some clashes have no fair winner. Two phones, no dates, both plausible. Two spellings of a name, each used once. Here the safe move is to keep the stronger-looking value in the main field and park the other where it stays findable: a second phone field, a notes field, or the audit file. Data parked is data saved. Data deleted on a coin toss is gone.
This is also where the cluster review earns its keep. In DedupeCSV you see the group side by side, pick the survivor, and fill its gaps from the other rows before you confirm. The rows you fold away go to the audit file with the group's ID and the reason, so “the other phone” is never more than a search away. If you clean files each month, save those choices as part of your recipe — see the plans for recipe limits by tier.
One clash to never guess on: identity itself. If two rows might be two people — same name, same town, thin proof — do not merge them at all. The choice “which record to keep” only applies once you are sure there is one person. When you are not sure, the answer is both records, flagged for a human look.
Special cases: money, consent, and IDs
Some fields carry more risk than others. Balances, order totals, and payment facts should never be merged by a dedupe rule at all. If two rows for one client hold two balances, that is a job for a human with the ledger open. Sum them, pick one, or split them — but by hand, with proof. Dedupe rules are for identity facts: names, contacts, addresses, tags.
IDs need the same respect. If each row has a client ID and the IDs differ, stop and think. Two IDs may mean two accounts — a personal one and a work one — that the client wants kept apart. Merging accounts can break billing, history, and trust at once. When IDs clash, default to “do not merge” and route the pair to a person who knows the account.
- Money fields: never auto-merge — hand-check with the ledger
- Consent fields: the safest, latest opt-out wins
- ID fields: a clash means stop, not merge
- Contact fields: newest wins, loser goes to the audit file
Two worked examples
Take a real pair. Row one: “Sam Ortiz, sam@old-firm.com, no phone, last touch two years ago.” Row two: “Samuel Ortiz, sam.ortiz@new-firm.com, phone on file, last order last month.” Same man, new job. The gaps fill first: the kept row takes the phone from row two and — with care — keeps row one's email in the audit file. The email that wins is the new one. The name that wins is the full one.
Why keep the old email at all? Because bounce risk is real. If the new mail comes back, the old one is your next try. This is the deep point of gap-filling with an audit trail: “keep” does not have to mean “throw the rest away.” It means “put the best fact in the main field, and park the rest where you can find it.” A merge is a sort, not a fire.
Second pair. Two rows for “Pat Kim.” One has a home street and a mobile phone. One has a work street and a desk phone. Both look fresh. This is not a clash to force. Keep the row that fits how you deal with Pat — mail goes to the home, say — and park the work facts in notes. Or keep both rows if Pat is in fact two clients' staff with one name. When in doubt, ask the human who knows the account.
Now the hard one: two rows, one name, one town, and a clash on the opt-out flag. One row says in. One says out. No dates. The rule from this guide is firm: the out wins. You can live with a missed mail to a keen client — you can fix it with one call. You cannot un-send mail to a person who told you to stop. When the cost of the two errors is not the same, pick the cheap error.
Pairs like these are born most often at import time, when two systems' views of one person land side by side. Why CRM imports create duplicates shows the causes. The cure on this page — fill gaps, trust dates, park the losers — is what keeps the next import from making the same pair twice.
Work a few pairs by hand and the rules stop being rules. They turn into a feel for the data: which source types with care, which fields go stale first, which clashes mean “two people” and which mean “one person, new job.” That feel is the real skill in this work. The rules just get you to it faster, with fewer burned pairs on the way.
Write the rules down, then reuse them
Every team makes these calls. Few write them down. So the calls get made anew each cleanup, by whoever is on duty, and the records drift. Write your order of trust on one page: which source wins, how dates rule, what happens to clashes, which fields are never auto-merged. Put the page where the cleanup happens. A one-page rule set turns a dreaded task into a routine one.
Then make the routine cheap to repeat. In a tool, the rule set is a recipe: survivor rule, gap-filling on, threshold set, once. Next month's file gets the same calls you made this month, in the same order, with the same care. That sameness is the real product. Anyone can delete rows once. Keeping the same standard every time is what keeps a list clean for years.
The survivor should be the best record, not the first one
When duplicates disagree, do not pick a row. Build a record: fill the gaps, let dates and fullness rule the clashes, honor the safest answer on consent, and park the losers in the audit file. The survivor that comes out is better than either row you started with — and every call you made can be shown and explained.
For the full cleanup flow around these choices, go back to how to remove duplicates from a CSV without losing data — it puts survivor choice in its place in the whole job.
Write your order of trust on one page and keep it with the list. The next person who runs the cleanup — quite possibly you, in six months — will thank you for it.
Sources and further reading
- Research Data Management Checklist — Stanford Lane Medical Library — Stanford's checklist on keeping good records: document choices and procedures so the same standard is used each time.
- Good recordkeeping year-round helps taxpayers avoid tax time frustration — IRS — The IRS on keeping records that support what you report — the same reason your merge choices should stay traceable.