‹Blog

How to Delete Duplicate Records in Airtable Without a Unique Key

FE
Filla Editorial•intermediate•Dec 15, 2024

The problem: duplicates without a unique key

You have duplicate records, but no single field is unique. For example, your table has:

  • DATE — some dates are the same
  • SUBJECT — some subjects repeat
  • NOTES — titles vary or repeat
  • TIME CREATED — duplicates may have different timestamps
  • FORMULA — creates different IDs even for duplicates

None of these fields alone can identify duplicates. You need a combination of fields to create a unique identifier.

Solution 1: Composite key formula (recommended for free plan)

Create a formula field that concatenates enough fields to uniquely identify records. Then use this field to identify duplicates.

Step 1: Create the composite key

Add a formula field (for example, {Composite Key}) that combines the fields that should be unique together:

{DATE} & "-" & {SUBJECT}

Or include more fields for better uniqueness:

{DATE} & "-" & {SUBJECT} & "-" & {NOTES}

Tip: Use a separator like "-" or "|" between fields to avoid false matches. For example, "Jan1" and "2024" concatenated becomes "Jan12024", but "Jan1" and "12024" also becomes "Jan12024" — adding a separator prevents this.

Step 2: Identify duplicates

Option A: Using the Dedupe block (paid plans)

If you have access to the Dedupe block:

  1. Install the Dedupe block
  2. Select your table
  3. Choose the {Composite Key} field (or multiple fields)
  4. The block will only consider records duplicates if all selected fields match

The Dedupe block lets you review and mark duplicates for deletion.

Option B: Using grouping (free plan)

  1. Create a new view
  2. Group by the {Composite Key} field
  3. Collapse all groups
  4. Look for groups with more than one record — these are duplicates

Manually review each group and delete the duplicates you want to remove.

Option C: Formula-based identification

For automated duplicate detection, create a formula that identifies duplicates:

First, create a lookup table:

  1. Create a new table called MasterLookup
  2. Add a single record called "Link All Records"
  3. In your main table, create a linked record field {MasterLookup} that links all records to this single master record

Then create these formula fields:

Composite Key field:

{DATE} & "-" & {SUBJECT} & "-" & {NOTES}

All Composite Keys (rollup):

// Rollup field that collects all composite keys
{MasterLookup} → Rollup of {Composite Key} with ARRAYJOIN(values)

Composite Key + Record ID (for uniqueness):

{Composite Key} & "-" & RECORD_ID()

All Composite Key + Record IDs (rollup):

// Rollup field that collects all composite key + record IDs
{MasterLookup} → Rollup of {Composite Key + Record ID} with ARRAYJOIN(values)

Duplicate? (formula field):

IF(
  {MasterLookup},
  IF(
    FIND(
      {Composite Key & Record ID},
      {All Composite Key & Record ID} & ""
    )
    =
    FIND(
      {Composite Key},
      {All Composite Key & Record ID} & ""
    ),
    BLANK(),
    "duplicate"
  ),
  "Record needs to be linked"
)

This formula checks if the record's composite key appears earlier in the list (meaning it's a duplicate). If it does, it marks it as "duplicate".

Step 3: Delete duplicates

Create a filtered view showing only records where {Duplicate?} contains "duplicate". Review the results, then bulk delete.

Safety tip: Make a backup copy of your base before deleting records.

Solution 2: Multiple field matching with Dedupe block

If you have the Dedupe block, you don't need to create a composite key formula. The Dedupe block lets you select multiple fields to check for duplicates.

  1. Install the Dedupe block
  2. Select your table
  3. Choose multiple fields (e.g., DATE, SUBJECT, NOTES)
  4. Records are only considered duplicates if all selected fields match exactly

This is simpler than creating formulas, but requires a paid plan with access to the Dedupe block.

Performance considerations

The formula-based approach (Solution 1, Option C) is resource-intensive, especially with many records:

  • With 100 records: 10,000 record-equivalents of data to process
  • With 400 records: 160,000 record-equivalents of data to process
  • With 1,000 records: 1,000,000 record-equivalents of data to process

The processing time increases significantly as your record count grows. Airtable needs time to calculate all the lookups and formulas. For large datasets, consider:

  • Using the Dedupe block (if available)
  • Processing in smaller batches
  • Using external tools like Integromat or Zapier for deduplication

Important caveats

Fields not included in composite key

The composite key method only considers fields you include in the formula. If you use DATE & SUBJECT but ignore NOTES, records with the same date and subject but different notes will be considered duplicates.

Solution: Include all fields that should be unique in your composite key formula.

Time zone consistency

If your DATE or TIME CREATED fields include time components, ensure time zones are consistent. Use UTC or the same time zone for all records to avoid false mismatches.

Empty values

Records with empty values in the composite key fields may all match each other. Filter out blank composite keys before deduplication:

// Filter: {Composite Key} is not empty

Automation for ongoing deduplication

If you're continuously adding records that might be duplicates:

  1. Keep the {MasterLookup} link and duplicate detection fields
  2. Automate linking new records to the master record using Integromat or Zapier:
    • Create a view filtered to records where {MasterLookup} is empty
    • Use automation to link new records to the master record
  3. Automate deletion of duplicates:
    • Create a view filtered to {Duplicate?} contains "duplicate"
    • Use automation to delete records in this view

Quick reference

Goal Approach
Create unique identifier Formula: {DATE} & "-" & {SUBJECT} & "-" & {NOTES}
Identify duplicates (paid) Use Dedupe block with composite key field
Identify duplicates (free) Group by composite key, look for groups with 2+ records
Automated detection Create MasterLookup table, use rollups and FIND formula
Bulk delete Filter view to duplicates, review, then delete

Example workflow

Scenario: You're importing notes from Apple Notes, and some notes are duplicated with slight variations.

  1. Create {Composite Key} = {DATE} & "-" & {SUBJECT} & "-" & LEFT({NOTES}, 50)
    • Using LEFT() to limit NOTES length to avoid formula length issues
  2. Group view by {Composite Key}
  3. Review groups with 2+ records
  4. Decide which record to keep (e.g., most recent, most complete)
  5. Delete the others
  6. For ongoing automation, set up the MasterLookup approach

References

This guide is based on community discussion: How To Delete Duplicate Records Without

How to Delete Duplicate Records in Airtable Without a Unique Key