How to Remove Duplicates and Prove What You Removed

Excel's Remove Duplicates button tells you 482 duplicate values found and removed; 61,518 unique values remain. Then it closes. You cannot see what left, you cannot get it back, and when somebody asks which 482 rows went, there is no answer.

The short answer

Remove duplicates with a tool that writes down what it removed. In DataTray, open Dedupe & Merge, add the file, pick the columns that define a duplicate, and choose how strictly to match — exact, normalized, or fuzzy. You choose which row survives rather than accepting whichever came first, and every run produces a removals file plus a report naming each dropped row, the row it lost to, and why. Your original is never modified.

The three ways two rows can be the same

Most duplicate problems are not exact duplicates, which is why exact matching feels like it does not work.

Exact — every selected column is byte-identical. Fast, safe, and catches only the duplicates created by running an export twice.

Normalized — case-folded, whitespace-collapsed, punctuation-stripped. Acme Corp., ACME Corp and acme corp become one. This is the highest-value default and the one most people actually want; it catches the bulk of real-world duplication without any judgement calls.

Fuzzy — string similarity above a threshold you set, so Acme Corporation and Acme Corp group together. This is where a human has to look, so results are presented as clusters to confirm rather than applied silently.

Choosing which row wins

"Whichever one the tool reached first" is not a rule, and it is the rule most tools use.

• First seen or last seen — fine when the file is in a meaningful order.
• Most complete — the row with the fewest empty fields. This is usually the right answer and almost never the default anywhere else.
• Highest or lowest value in a column you name — the most recent updated_at, the largest order value.

Or do not drop them at all

The case that turns deduplication from a cleanup into a gain: two records for the same customer, one holding the phone number, the other holding the email.

Merge mode coalesces the group instead of discarding the losers, taking the first non-null value for each field. One record comes out, more complete than either input. If you have ever deduplicated a list and then discovered the data you needed was on the row that got dropped, this is the setting you wanted.

The removals file is the point

Every run writes what it removed, beside what it kept. That single behaviour is the difference between a deduplication you can defend and one you have to take on trust:

• You can check the matching was right before you rely on the result.
• You can hand somebody the file when they ask what happened to their 482 rows.
• You can recover a row that should not have gone, because it is still there.

Losing rows silently is the failure mode that makes people distrust deduplicated data — usually correctly.

Google Sheets, honestly

how to remove duplicates google sheets is the most-searched version of this question, and DataTray cannot help with it directly — it is a Windows desktop app and works on files on your machine. Export the sheet to CSV or Excel first and everything above applies. If you need it to happen inside Sheets, use the built-in UNIQUE function or Data → Data cleanup instead; that is a genuinely better fit for that workflow.

On very large files

The file that most needs deduplicating is usually the one Excel already refused to open. Because DataTray queries the file on disk rather than loading it into memory, the row count is not what decides whether the job is possible.

Fuzzy matching on a large file uses a blocking key so it does not degrade into comparing every row against every other row, which is what makes it usable rather than theoretically possible.

---

Every other free way to do this asks you to upload the file first. A customer list is a poor candidate for that.