# Address Deduplication: Finding Duplicate Customer Records

> Address deduplication finds records that describe the same delivery point or the same customer despite different spelling. Normalise first (case, abbreviations, punctuation, spacing), build a match key, compare candidates only within the same postcode, review borderline pairs, and merge with clear rules about which record's fields win.

- URL: https://address-valid.com/data-quality/address-deduplication/
- Language: en
- Updated: 2026-10-19
- Publisher: AddressValid (OnlineBuilders LLC, https://onlinebuilders.biz)

The house in the photo has three numbers on its wall: 78, 34, and the old 16. One building, three ways to write its address, and all three may still appear somewhere in a customer database. That is address deduplication in one picture. The data is not wrong exactly; it is the same thing recorded differently.

Duplicates come from predictable places. Guest checkouts create a new customer per order. Marketplace imports arrive with their own formatting. Customers who move get a second record. Staff retype a phone order instead of searching. Each source produces a different kind of duplicate, and one matching rule rarely catches all of them.

## Step one: normalise before you compare

Comparing raw strings finds almost nothing. `27 Harbour Road, Flat 3` and `Flat 3, 27 Harbour Rd.` are the same flat, but no exact comparison says so. Normalisation turns both into one canonical form:

- uppercase everything
- remove punctuation (full stops, commas, `#`)
- collapse multiple spaces, strip leading and trailing ones
- map street types and unit words to one form: ROAD to RD, SUITE to STE
- strip spaces from postcodes, or format them canonically
- transliterate where needed: ß to SS, Ü to UE, if your data mixes both

The abbreviation list should follow the postal standard of the country. For US addresses, USPS Publication 28 lists the standard suffixes and unit designators; our [US address format](https://address-valid.com/address-formats/us-address-format/) article has the common ones. Running addresses through a validation service first is even better, because it returns the standardised form directly; see the [address validation API comparison](https://address-valid.com/validation/address-validation-api/).

## Step two: build a match key

Match keys are short strings that are identical for records you consider candidates. Here is a deliberately simple version in JavaScript, which we ran on five sample rows:

```
const key = (line1, line2, postcode) => {
  const words = `${line1} ${line2}`.toUpperCase().replace(/[.,#]/g, ' ')
    .split(/\s+/).filter(Boolean).map((w) => ABBR[w] ?? w);
  const nums = words.filter((w) => /\d/.test(w)).sort();
  const rest = words.filter((w) => !/\d/.test(w)).sort();
  return `${postcode.toUpperCase().replace(/\s+/g, '')}|${nums.join(' ')}|${rest.join(' ')}`;
};
```

The output, with `ABBR` mapping ROAD, DRIVE and SUITE to their short forms:

| Input (line 1, line 2, postcode) | Match key |
|---|---|
| 27 Harbour Road, Flat 3, BS1 6XY | `BS16XY\|27 3\|FLAT HARBOUR RD` |
| Flat 3, 27 Harbour Rd., (empty), bs16xy | `BS16XY\|27 3\|FLAT HARBOUR RD` |
| 27 Harbour Road, Flat 4, BS1 6XY | `BS16XY\|27 4\|FLAT HARBOUR RD` |
| 4500 Riverside Drive, Suite 210, 22162 | `22162\|210 4500\|DR RIVERSIDE STE` |
| 4500 Riverside Dr, Ste 210, 22162 | `22162\|210 4500\|DR RIVERSIDE STE` |

The first two rows collapse into one key, as they should. Flat 4 stays separate, as it should. The addresses are invented for the test.

Sorting the words makes the key independent of word order, which is what catches `Flat 3, 27 Harbour Rd`. It also has a cost, and you should know it: `3 Harbour Road, Flat 27` produces the same key as `27 Harbour Road, Flat 3`. That is why a match key only finds candidates. Then a field-by-field comparison decides.

## Step three: compare within blocks

Comparing every record with every other record does not scale. The number of pairs grows with the square of the list size.

**Why blocking matters: pairwise comparisons for 100,000 records**

- All pairs: 100,000 × 99,999 ÷ 2: 4,999,950,000
- Split into 1,000 postcode blocks of 100 records: 1,000 blocks
- Pairs per block: 100 × 99 ÷ 2: 4,950
- **Pairs with blocking: 4,950,000**

Even block sizes assumed for illustration; real postcode blocks vary in size.

Blocking by postcode cuts the work by a factor of about a thousand in this example. The trade-off: records with a wrong postcode never meet their duplicate. Running a second pass blocked on a different field, such as normalised street name plus house number, recovers some of those.

Within a block, use similarity instead of equality. Edit distance (the number of single-character changes between two strings) handles typos well: `HARBOR` and `HARBOUR` differ by one. Set a threshold relative to the string length, then send pairs just above and just below it to a human for review. That review queue is where you learn whether your threshold is right.

**Deduplication pipeline**

1. **Normalise** Case, punctuation, abbreviations, postcode format.
2. **Standardise** Optional: validate against postal data for canonical addresses.
3. **Block** Group by postcode; second pass by street plus number.
4. **Score pairs** Exact key match, then similarity on name and address fields.
5. **Review** Borderline scores go to a person, not to auto-merge.
6. **Merge** Apply survivorship rules, keep history, link old IDs to the new one.

## The false positives that hurt

Merging two different customers is worse than leaving a duplicate. Each duplicate costs you one extra catalogue. False merges mix two people's order histories and possibly their personal data.

The classic traps:

- Households. Parents and adult children with the same surname at the same address are different customers.
- Apartment buildings without unit numbers. Two people at `27 Harbour Road` without flat numbers may live on different floors. Missing units are a data quality problem in their own right; [address line 2](https://address-valid.com/address-formats/address-line-2/) explains how to collect them.
- Shared business addresses. Co-working spaces and office towers host hundreds of companies at one street address.
- Junior and senior. Same name, same address, different people.

A practical rule: an address match alone never merges customers. It needs a second signal, such as the same email, the same phone number or a name similarity above a high threshold.

## Survivorship: which record wins

Once two records are confirmed duplicates, decide field by field what the merged record keeps. Common rules: the most recently verified address wins over the oldest; a validated address wins over an unvalidated one; the email with a confirmed opt-in wins; order history is combined, not replaced. Keep the old record IDs as aliases, so links from invoices and support tickets still resolve.

Moves complicate this. If one record holds a customer's old address and another the new one, deduplication and move updates overlap. Run a change-of-address update first, as described in our article on [national change of address](https://address-valid.com/data-quality/national-change-of-address/), then deduplicate. Otherwise you risk merging toward the old address.

For one-off checks of individual entries, our [address format checker](https://address-valid.com/address-checker/) shows how a single address should look before you standardise it. Deduplicating a CRM with millions of records, several source systems and merge history is a data engineering project; studios such as [OnlineBuilders](https://onlinebuilders.biz/solutions/data-mining) build matching pipelines of that kind.

Start small: export your customer table, build the normalised match key in one extra column, and count how many keys occur more than once. That count is your first honest estimate of the duplicate problem.

## FAQ

### Can I deduplicate addresses in Excel?

For small lists, yes: normalise the address in a helper column with UPPER, TRIM and SUBSTITUTE, then use Remove Duplicates or COUNTIF on that column. Excel only finds exact matches on the helper column, so near-duplicates with typos remain.

### Should two people at the same address be merged?

Usually not. Same address with different names is a household, not a duplicate customer. Deduplicate at the person level for customer records and at the household level only for mailings where one piece per address is enough.

### What is fuzzy address matching?

Comparing strings by similarity instead of equality, for example with edit distance, which counts the character changes needed to turn one string into another. It catches typos like Harbor and Harbour but needs a threshold and a review step to avoid false merges.

### How often should I deduplicate customer data?

Run a full pass after every import or migration and a lighter check at the point of entry, so a returning guest customer is matched to the existing record instead of creating a new one.

Photo: Tangopaso, Public domain (https://commons.wikimedia.org/wiki/File:Triple_house_numbers_(Chelles).jpg)