Excel List Compare & De-duplicate

About this tool

Comparing two lists looks like a solved problem until you try it on real data. The hard part is never the loop — it is deciding when two strings are the same string. "Acme Corp." and "ACME CORP" are obviously the same vendor to you and to me, and obviously different to a strict equality test. Every wasted afternoon reconciling a spreadsheet starts here.

So every entry is folded into a normalised key before anything is compared. Full-width characters become half-width, so a name typed on a Japanese IME matches the same name typed on an English one. Runs of spaces collapse and the ends are trimmed, because a stray trailing space is invisible and should not create a phantom entry. Zero-width and other invisible characters are removed outright — they are the classic reason two cells look identical and still do not match. Case is folded, and you can turn any of these off if you actually need the strict behaviour.

Near-duplicates are a separate question, and a separate section. Normalisation cannot fix a typo: "Jon Smith" and "John Smith" are different keys, and no amount of Unicode folding will join them. So an edit-distance pass flags pairs that are close but not equal, and reports the similarity so you can judge. The most useful outcome is the cross-list case: an entry that appears only in A and an entry that appears only in B, one letter apart — almost certainly the same thing, entered twice with a slip. The tool points at it; you decide.

Nothing is changed for you. The original spelling is what the results show, exactly as you typed it, with the normalised key shown alongside only when it differs — so the reason two entries matched is visible rather than implied. And no near-duplicate is ever merged or dropped automatically: a suggestion you overrule is far cheaper than a merge you did not notice.

Why near-duplicate suggestions stop at 300 entries

Checking every entry against every other is quadratic, so the near-duplicate pass is skipped once either side holds more than 300 distinct entries. Exact matching, grouping and de-duplication still run, and the page states plainly that the suggestion pass was skipped.

Exact matching is close to linear: each entry is folded into a key and looked up. Near-duplicates are not. Every candidate has to be measured against every other, which is fine for a few hundred rows and hopeless for a supplier master file.

The limit is counted per side and suppresses only the suggestion groups — inside list A, inside list B, and between the two. The counts of what appears on both sides, what is unique to each and what repeats within one list are produced as normal, because those do not depend on the pairwise pass.

The cross-list comparison carries its own guard, since its work is the product of the two sides: a hundred entries against a hundred is ten thousand measurements. Those pairs are sorted by similarity and only the closest are kept, so the cap trims the least useful rows rather than an arbitrary slice.

Why one name can be left out of a near-duplicate group

Groups are formed greedily in the order entries first appear. The first entry becomes the anchor of its group, and a later entry joins only if it is close enough to that anchor — being close to something already in the group is not enough.

Greedy clustering is what keeps the output cheap and predictable. It also has a consequence worth knowing: similarity is not transitive. Three names can run A close to B, B close to C, and A far from C — and C will stay out of the group, because only the anchor is measured.

The order of the list therefore influences the groups. Whichever entry appears first anchors a group and collects its near matches, so the same list pasted in a different order can group them differently. That is why the results follow the order you pasted rather than an order the tool chose.

Nothing is merged here. A group is a suggestion that these entries may be the same item, shown with the original spellings intact, and it never removes an entry from the exact lists above it.

Why a different length rules an entry out early

Two entries whose lengths differ by more than the threshold permits are never compared at all. At the default 85 percent, a difference of more than about a seventh of the longer name cannot reach the threshold, so the expensive comparison is skipped.

The edit distance between two strings is at least the difference in their lengths: you cannot grow a five-letter name into a nine-letter one at no cost. That gives a cheap ceiling on similarity, and the check is done before any letters are examined.

The bound earns its keep because the measurement itself is the expensive part. With a hundred entries on each side, most pairs differ wildly in length and can be dismissed by an arithmetic test instead of a full edit-distance table.

It also explains an absence. If a name you expected to be paired up is missing from the suggestions, the first thing to check is length: at a strict threshold, a long name and a short one are disqualified before their characters are ever examined, however many of those characters they share.

A transposition counts as one error, not two

Micheal against Michael, teh against the — swapping two adjacent letters is the commonest typing slip there is. Plain edit distance scores it as a delete plus an insert, two steps, which falls straight through a 0.85 similarity threshold and is called unlike.

This page therefore uses an edit distance that permits adjacent transposition, the OSA variant of Damerau-Levenshtein, where a swap costs one step. That single change decides whether a mistyped version of the same person is recognised at all — and if it is not, the list comparison has failed at its job.

Distance is measured in code points rather than UTF-16 units. Otherwise some characters, including emoji and CJK beyond the basic plane, are split in half, the distance inflates, and two identical names are reported as different.

A length difference beyond what the threshold allows rules the pair out before any distance is computed. That is not an optimisation but the judgement itself: two strings six characters apart cannot be one person typo, and computing further only yields a precise-looking meaningless number.

Comparison uses the normalised value and display uses what you wrote. The report always shows the original spelling, because those differences are precisely what you are checking; normalising the display would hide the very thing under examination.