Remove duplicates, UNIQUE and COUNTIF only catch values that are exactly the same. Real lists are full of near-duplicates: "Acme Pty Ltd", "ACME Pty. Ltd" and "Acme Pty Limited" are one customer typed three ways, and "Initec" is probably a typo for "Initech". This page finds them in two passes: a cleaned key that makes formatting differences disappear, then a similarity score for the typos that are left. Everything is formulas, and your original data stays untouched.
Row 1 holds headers. Names in A2:A8:
Acme Pty Ltd
ACME Pty. Ltd
Globex Corporation
Initech
Acme Pty Limited
Globex Corp
Initec
Most near-duplicates differ only in capitals, punctuation, spacing and company suffixes. Build a key with all of that removed. In B2, filled down:
=IF(A2="","",TRIM(REGEXREPLACE(REGEXREPLACE(LOWER(SUBSTITUTE(A2,CHAR(160)," ")),"[^a-z0-9 ]",""),"\b(pty|ltd|limited|inc|llc|corp|corporation|co|company|the)\b","")))
The keys are acme, acme, globex, initech, acme, globex, initec. Reading from the inside: SUBSTITUTE swaps non-breaking spaces for normal ones, LOWER removes capitals, the first REGEXREPLACE deletes everything except letters, digits and spaces, the second deletes the suffix words, and TRIM tidies the spaces left behind. Edit the word list to suit your data, and check that no real name is on it. The key also drops accented letters, so "Café" becomes "caf"; that is fine for matching, as long as both copies lose the same letters.
Count each key. In C2:
=COUNTIF($B$2:$B$8,B2)>1
TRUE marks a near-duplicate: the three Acme rows and the two Globex rows. Initech and Initec show FALSE, because their keys still differ.
To label each group with the first spelling seen, in D2:
=INDEX($A$2:$A$8,MATCH(B2,$B$2:$B$8,0))
This gives Acme Pty Ltd for all three Acme rows, Globex Corporation for both Globex rows, and the name itself for the others. Use column D as the clean value, or as the key for any of our exact methods, such as merging rows and adding up their amounts: https://www.snakbacks.com/merge-duplicate-rows-sum-google-sheets
Cleaning cannot fix a misspelling inside a word. For that, score each key against every other key and keep the best score. This uses the LEVENSHTEIN named function from our Levenshtein guide, which takes a minute to set up: https://www.snakbacks.com/levenshtein-distance-google-sheets (or get it ready-made in our free starter template: https://snakback.gumroad.com/l/levenshtein-starter)
In E2, filled down:
=IF(B2="","",MAX(MAP($B$2:$B$8,ARRAYFORMULA(ROW($B$2:$B$8)),LAMBDA(x,rn,IF(OR(rn=ROW(),x=""),0,1-LEVENSHTEIN(B2,x)/MAX(LEN(B2),LEN(x)))))))
Results: 1, 1, 1, 0.857, 1, 1, 0.857. A score of 1 means another row has the same key (Pass 1 already found those). Initech and Initec score 0.857 against each other, one letter apart. The rn=ROW() test skips the row's own key, which would always score 1. Keep the ARRAYFORMULA around ROW: without it, ROW of a range returns only the first row number, MAP gets two inputs of different sizes, and the formula returns #VALUE!. We hit exactly that in testing.
Turn the score into a decision in F2:
=IF(E2="","",IF(E2=1,"duplicate",IF(E2>=0.85,"check","")))
"check" is the useful column: likely typos, worth a look before you merge them. Do not merge them automatically. One letter is a large share of a short name, so two genuinely different short names can pass 0.85. Raise the cut-off for short keys, or always review "check" rows by eye.
When columns D and F look right, make a copy of the tab and turn column D into fixed values (copy it, then Edit > Paste special > Values only), so deleting rows cannot change the labels. Then either:
keep the first row of each group with Data > Data cleanup > Remove duplicates, ticking only column D, or
build a clean list with =UNIQUE(D2:D8), which returns Acme Pty Ltd, Globex Corporation, Initech, Initec.
Fix any "check" rows by hand first (here, change Initec to Initech) and the list collapses to three.
The similarity formula scores every pair, so the work grows with the square of the list. A few hundred rows is comfortable; thousands will be very slow.
It finds the closest other row but does not group chains of typos: if A is close to B and B is close to C, these formulas will not put all three in one group.
Reordered words ("Smith John" and "John Smith") score low. Sort the words inside the key if order does not matter, as shown in our names guide: https://www.snakbacks.com/match-names-with-typos-google-sheets
Disclosure: Snakbacks, the publisher of this site, sells a paid script pack and makes two free add-ons for Google Sheets.
The two passes above are free and fine for a few hundred rows. The Data Cleanup Kit for Google Sheets (US$15, no add-on install): https://snakback.gumroad.com/l/data-cleanup-kit does both in one function: FUZZYDUPES(range) groups near-duplicates, chains included, and labels each group, on up to 600 rows at a time. It also has ready-made tabs for merging and summing, keeping the latest row, comparing lists, splitting names and cleaning text.
Our free add-on Duplicate Remover is coming soon to the Google Workspace Marketplace.
Snakbacks publishes the Duplicate Remover and Fuzzy Match add-ons.