Find Near-Duplicate Rows in a TSV

similarity threshold · review the groups first · remove, flag, or list · in your browser

Exact dedupe only catches rows that already agree. Real duplicate records disagree: Ada Lovelace, ada  lovelace, Lovelace, Ada and Ada Lovelcae are four spellings of one person and no exact match will merge them. This normalises the text hard, scores what remains with an edit distance, and groups the rows that come out close enough — and by default it shows you the groups before anything is deleted.

How to use

1. Paste or drop the file. Leave the mode on review the groups — the output is then only the rows that clustered, with each one's similarity score, so you can judge the threshold before you act on it.
2. Name the key columns. Blank compares the whole row, which is rarely what you want for fuzzy matching: a differing updated_at drags every score down. name,email or just company gives far sharper clusters.
3. Tune the threshold. 88% is a good start. Raise it if unrelated rows are being grouped; lower it if you can see duplicates it missed.
4. Pick a survivor rule. The fullest record is usually right — fuzzy duplicates are often the same row with some fields left empty.
5. Then switch the mode. Remove the duplicates emits the cleaned file; flag every row keeps all of them and prepends dup_group, dup_role and dup_similarity columns so you can sort or filter in a spreadsheet instead.

What "88% similar" actually means

Each row is reduced to one comparison string: the key columns, joined, with whatever you told it to ignore stripped out. Two of those strings are then scored as 1 − distance / length, where the distance is the Levenshtein edit distance — the number of single-character insertions, deletions or substitutions needed to turn one into the other — and the length is that of the longer string.

So on a 25-character key, 88% allows three edits: a transposed pair, a missing letter and a stray one. On a 5-character key it allows none at all, which is why very short keys behave as exact matches. If you are matching something short, like a postcode or a three-letter code, lower the threshold or add a second key column instead of fighting it.

Normalise first, score second

Nearly all of the work is done before any scoring happens, because the differences that matter least are also the cheapest to remove. Casing, runs of whitespace, punctuation and accents are all normalised away — after which José García and jose garcia. are byte-identical and cost nothing to match. Leave those four on unless you have a reason.

Everything except digits is the exception, and a powerful one: it throws away all non-digit characters, which turns +44 (0)20 7946 0958 and 044-2079460958 into the same string. Use it on a phone or account-number column and the threshold barely matters. Do not use it on a whole row.

Why it does not compare every pair

Comparing every row with every other row is quadratic: 10,000 rows is fifty million comparisons, and each one is an edit-distance calculation. That hangs the tab. Instead, rows are bucketed twice — once on the first four characters of the key, and once on the key's tokens sorted alphabetically — and only rows sharing a bucket are compared.

The second bucket is what catches reordering: Lovelace, Ada and Ada Lovelace share no prefix, but their sorted tokens are identical. The status line reports how many pairs were actually compared, which is usually a tiny fraction of the theoretical total. The trade is real though: two rows that share neither a prefix nor a token set will not be found, no matter how the threshold is set. Above 20,000 rows the input is capped and the status line says so.

FAQ

Privacy

100% client-side. No upload, no signup.