Remove Duplicates Keeps the Wrong Row
Remove duplicates deletes the copy you wanted to keep and keeps the outdated one. The tool keeps the first occurrence it finds, so sorting first decides which row survives.
TL;DR / Quick Fix
Quick fix: before removing duplicates, sort the data so the row you want to keep comes first in each duplicate group, such as newest date first. Then run remove duplicates on a copy of the sheet and verify the surviving rows before you save.
Follow these steps
- Copy the sheet first. Duplicate the sheet or the data before you start. A copy is your undo button if the wrong rows survive.
- Decide which row should survive. For each group of duplicates, know the keeper: the newest date, the most complete row, the verified entry. The rule decides the sort.
- Sort so keepers come first. Sort the data so the keeper row sits first within each duplicate group. Remove duplicates keeps the first occurrence it meets.
- Choose the right columns. In the dialog, check only the columns that define a duplicate. Too many columns means nothing matches, while too few merges rows that differ.
- Run remove duplicates. Start the tool and read its report of how many duplicates it removed. The count should match what you expected to lose.
- Verify the survivors. Spot-check several groups and confirm the keeper row survived. A quick check now beats discovering the wrong survivor later.
What NOT to do
- Do not run remove duplicates on the only copy. Work on a copy, since the deletion cannot be undone after you save.
- Do not skip the sort. Without sorting, the survivor is whichever row happened to be first, which is rarely the one you want.
- Do not check every column blindly. Choose the columns that truly define a duplicate for your data.
- Do not trust the removal count alone. Spot-check survivors, since the count tells you how many went, not which ones stayed.
Frequently asked questions
Which duplicate row does the tool keep?
The first occurrence it finds, so sorting first decides which row survives.
How do I keep the newest duplicate?
Sort by date newest first within each group, then run remove duplicates.
Can I undo remove duplicates?
Only before saving, or from your backup copy. That is why you copy the sheet first.