The problem: dedupe thousands of records fast

You have a table with records coming from many sources and obvious duplicates (for example, duplicate phone numbers). The built-in Dedupe extension is great for small batches but it still requires manual review. Here is a repeatable way to select the single record to keep per key and remove the rest in bulk.

---

Key idea

Normalize the duplicate key into its own linked table, compute the keeper per key in that table, pull the keeper back to the main table, flag non-keepers as duplicates, and then bulk delete.

Works for keys like phone, email, and normalized name+dob.

---

Step 1 — Choose your dedupe key

Pick a field that defines “same record”. Examples:

• Phone (normalized to digits only)
• Email (lowercased, trimmed)
• UniqueKey (a concatenation like LOWER(Email) & PhoneLast4)

Normalization helpers:

// Strip non-digits from phone
REGEX_REPLACE({Phone Raw}, "[^0-9]", "")

// Lowercase and trim email
LOWER(SUBSTITUTE({Email Raw}, " ", ""))

// Composite key
LOWER({Email}) & "-" & RIGHT({Phone}, 4)

Create a single field (for example {Dupe Key}) that holds your normalized key.

---

Step 2 — Link keys into a new table

1. Duplicate {Dupe Key} to {Dupe Key (Link)}
2. Change {Dupe Key (Link)} to “Link to another record”, create a new table Keys
3. Airtable will create a record in Keys for every unique key and link all matching rows

Result: Keys has one row per key with links to all candidate duplicates.

---

Step 3 — Decide your keeper rule

Common rules:

• Newest record wins (by {Created At} or {Updated At})
• Oldest record wins
• Most complete record wins (highest number of non-blank fields)

In Keys, add rollups to compute the keeper value:

• Newest: Rollup the main table’s {Created At} with MAX(values)
• Oldest: Rollup {Created At} with MIN(values)
• Most complete: First add a “completeness score” in the main table, then rollup with MAX(values)

Completeness score (example):

// Count non-blanks across important fields
IF({Name},1,0)
+ IF({Email},1,0)
+ IF({Phone},1,0)
+ IF({Address},1,0)
+ IF({Company},1,0)

Name this field {Score} and roll it up in Keys with MAX(values).

---

Step 4 — Pull keeper value back to the main table

In the main table, add lookups of the rollups you created in Keys:

• {Keeper Created At} = Lookup of Keys → Max Created At
• {Keeper Score} = Lookup of Keys → Max Score

Now create a formula {Is Keeper} to mark the single row that should remain:

Newest rule:

IF(
  AND({Created At}, {Keeper Created At}),
  {Created At} = {Keeper Created At},
  FALSE()
)

Most complete rule:

IF(
  AND({Score}, {Keeper Score}),
  {Score} = {Keeper Score},
  FALSE()
)

Tie-breakers (optional): If multiple rows share the same max score, add a secondary comparison (for example, latest {Updated At}) using an AND() clause.

---

Step 5 — Flag duplicates and bulk delete

Add {Is Duplicate} formula:

IF({Is Keeper}, FALSE(), TRUE())

Create a filtered view “Duplicates” where {Is Duplicate} = 1. Spot check a few groups by grouping on {Dupe Key}. When satisfied, select all in that view and delete.

Safety tips:

• Make a copy of your base before deleting
• Add a temporary checkbox {Ready To Delete} and filter on it for an extra confirmation step
• Exclude empty keys in filters to avoid mass-deleting blank-key rows

---

Optional — One-click cleanup with automation

You can replace the manual delete with an automation:

Trigger: when {Is Duplicate} = 1 and {Ready To Delete} = 1

Action: “Delete record” on the current record

This gives you a controlled, repeatable flow without manual review.

---

Quick reference

Goal	Fields and formulas
Normalize key	{Dupe Key} with REGEX_REPLACE and LOWER
Group by key	Convert {Dupe Key} copy to a linked field → new Keys table
Keeper (newest)	In Keys, Rollup {Created At} with MAX(values)
Keeper (most complete)	{Score} sum of IF({Field},1,0) then Rollup MAX(values)
Mark keeper	{Created At} = {Keeper Created At} or {Score} = {Keeper Score}
Delete safely	View filtered to {Is Duplicate} = 1 plus a confirmation checkbox

---

Pitfalls to avoid

• Empty keys: filter out blank {Dupe Key} before deduping
• Mixed time zones: ensure {Created At} and rollups are consistent (prefer UTC toggle)
• Non-deterministic ties: define a secondary tie-breaker to avoid keeping multiple rows

---

Opinionated take

This pattern uses native building blocks and scales well. It also leaves an audit trail in formulas and rollups so you can explain which record was kept and why. For recurring imports, keep the fields and views in place so you can re-run the same workflow in minutes.

---

Further reading

• Community context: Remove duplicates without having to look at them 1 by 1

---