← All Kits · SQL Kit

Fuzzy Matching Names: When Two Spellings Are the Same Company

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

ACME Ltd, Acme Limited, ACME LTD., and Acme Ltd trading as Acme. Four rows, one customer, four separate totals, and a revenue report that ranks your largest client fourth.

What you do: work the ladder in order. Standardise, then exact match, then block, then score. Most people start at scoring, which is the expensive end and the one with the most false merges.

The short version. Cleaning turns most fuzzy problems into exact ones, and exact matching cannot make a mistake.

Stage 1: standardise

One pass over the field, applying the same transformations to both sides:

-- SQL, one expression at a time so each step is visible
UPPER(TRIM(REPLACE(REPLACE(name, '.', ''), ',', '')))

# pandas
s = (df['name'].str.upper()
       .str.replace(r'[^\w\s]', '', regex=True)
       .str.replace(r'\b(LIMITED|LTD|PLC|INC|LLC)\b', '', regex=True)
       .str.replace(r'\s+', ' ', regex=True)
       .str.strip())

Keep the standardised value in a new column. Never overwrite the original: the original is what you show a human during review, and it is the only way to explain a merge afterwards.

Stage 2: exact match on the standardised value

Join on the cleaned column and see how much is left. On business names this typically resolves the large majority, because most variation is punctuation, case and legal suffix rather than genuine misspelling. Everything that matches here is certain, which is the reason to do it before anything clever.

Stage 3: block, so scoring is affordable

Comparing 10,000 names to each other is 49,995,000 pairs. Blocking means only comparing rows that share a cheap key, so you never form most of those pairs:

• First three characters of the cleaned name
• Postcode, or the outward part of it
• Phone number digits, last six
• Email domain

Blocking on the first three characters splits the list into hundreds of small groups, and the comparison count falls by orders of magnitude. The cost is that a pair whose first three characters differ is never compared, so use two different blocking keys and take the union of the results.

Stage 4: score what is left

In Postgres, pg_trgm gives you similarity(a, b) and an index that makes it usable. In Python, rapidfuzz is the current standard and is fast enough to run over a blocked list on a laptop. In Excel, there is no native fuzzy function, but Power Query has a Fuzzy Matching option on a merge with an adjustable similarity threshold, which is the practical route for a one-off.

The false merges to expect

Fuzzy matching is confidently wrong on exactly the cases that matter:

• Branches. ACME LONDON and ACME LEEDS score highly and are different sites, sometimes different legal entities.
• Numbered entities. PROJECT CO 3 LTD and PROJECT CO 8 LTD differ by one character and are unrelated companies.
• Common surnames. J SMITH and J SMYTH at different addresses.
• Genuinely similar names. Two real firms in the same trade with near-identical names, which is more common than it sounds.

The defence is a second field. Never merge on name alone: require agreement on a postcode, a registration number, a domain or a phone number. One extra field collapses the false merge rate dramatically, because two records agreeing by accident on two fields is rare.

Recording what you did

Every merge needs a row in a crosswalk table: source id, target id, the score, the method, the date, and who approved it. Without it, the merge is irreversible and unexplainable, and the first time somebody asks why two customers became one you will be reconstructing it from memory.

How to apply this to your own work

1. Standardise into a new column and measure how many duplicates that alone resolves. Report that number; it is usually the majority.
2. Do not score anything until you have blocked. It is the difference between seconds and hours.
3. Pick a second field to require agreement on before any merge.
4. Label 100 pairs by hand to set your thresholds. It takes an hour and it is the only way to know your numbers mean anything.
5. Keep the crosswalk. It is the difference between a decision and an accident.

The one habit to keep

Review a sample of the automatic merges, not only the uncertain band. The high-confidence merges are the ones nobody looks at, which is exactly why an error there survives longest.

How would you undo the last automated merge in your customer table?