How to Find Duplicates in a File Too Big for Excel

Excel's Remove Duplicates would be fine, except the file has four million rows and Excel will not open it. This is the ordinary case, not the edge case: duplication accumulates in exactly the files that grow.

The short answer

Group the rows without loading the file. In DataTray, open Dedupe & Merge, add the file, and choose the columns that define a duplicate. It runs against the file on disk, so four million rows is no different from four thousand, and every run writes a report naming what it found.

Look before you remove

Removing is a decision. Counting is not, and it is usually the right first step.

Open the file in Data Explorer and look at the Columns tab. The profile reports, per column, how many distinct values there are against how many rows — which tells you immediately whether your supposed identifier is actually unique, and it reports candidate keys directly.

A column that should be unique and is 97% distinct is the interesting finding. That is not a deduplication job yet; it is three percent of your rows telling you something went wrong upstream.

Three definitions of "the same"

Exact — every selected column byte-identical. Catches re-runs of the same export. Fast and safe.

Normalized — case-folded, whitespace-collapsed, punctuation-stripped. Acme Corp. and ACME Corp become one. This is the setting most people actually want and it catches the bulk of real duplication.

Fuzzy — string similarity above a threshold, so Acme Corporation and Acme Corp group together. On a large file this 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.

Fuzzy results are presented as clusters to confirm. That is deliberate — similarity is a judgement call, so it is not unattended work.

Which copy survives

Not "whichever the tool reached first", which is the rule most tools use without saying so.

Choose most complete — the row with the fewest empty fields — or the highest or lowest value in a column you name, such as the most recent updated_at. Or switch to merge mode, which coalesces the group field by field and takes the first non-null value for each, so the surviving record is more complete than any of its inputs.

That last one is the case where deduplication stops being cleanup and becomes a gain: one record has the phone number, another has the email, and you want both.

Keep the evidence

Every run writes a removals file listing each dropped row, the row it matched, and why.

On a four-million-row file this is not optional in any real sense. Nobody can eyeball that result to check it, so the report is the check — and it is what you hand over when somebody asks what happened to their rows six weeks later.

If the duplicates are near-misses in one column

Sometimes the problem is not duplicate rows but inconsistent values — twelve spellings of "California" in a state column. That is a cleaning job rather than a deduplication one: Clean & Anonymize clusters a low-cardinality column against its own values and standardises them, which frequently turns a messy fuzzy-dedupe problem into a clean normalized one.

Do that first if it applies. Deduplicating consistent data is much more predictable.

---

Every other free way to do this asks you to upload the file first — and a four-million-row customer list is the worst possible thing to hand to a web form.