Help

This Product Ideas board is currently undergoing updates, but please continue to submit your ideas.

[SOLVED] Record Duplication Detection, Deduping, and Duplicate Merging

cancel
Showing results forย 
Search instead forย 
Did you mean:ย 
Aaron_Owen
7 - App Architect
7 - App Architect
Status: Launched

I would love to have a feature that would search a given table by a field or set of fields to find and surface duplicate rows. It would then be great to be able to take a pre-defined action on duplicated rows such as delete, or merge. Dealing with dupes is always a pain with large data sets or importing new data into a set where some records may already exist. A way to deal with dupes in a somewhat automated way would be really helpful.

93 Comments
Sydney_Brown
5 - Automation Enthusiast
5 - Automation Enthusiast

Are any of you using duplicate preventing processes or procedures youโ€™d recommend beyond - โ€œHey, check the DB before you add a new record?โ€ I am setting up a system and have yet to release it to the team. When I do, I would like to specify procedural guidelines for quality data curation. Thank you.

William_Pitcher
4 - Data Explorer
4 - Data Explorer

Duplicate checking is important when collaborating. As far as I can tell, if I share a form, invitees canโ€™t see if a name has already been added to a base, and they are able to re-add a name without warning. They are submitting names of people that could be donation prospects, and some of our best prospects will be recommended multiple times.

It appears I would really need to share the base in grid view rather than a form.

David_Smedberg
6 - Interface Innovator
6 - Interface Innovator

Hi all! I found a workaround to help find and delete duplicate records. As is, this only works for deleting perfect duplicates, it does NOT merge records together into a single record.

Imagine a table with 3 records: John Smith, John Smith, and Abraham Lincoln.

First, create an auto number field.

Next, ensure you have a single line text field that contains the same text for each duplicate record (โ€œJohn Smithโ€) but is unique for records that are not duplicates (โ€œAbraham Lincolnโ€).

Next, turn this field into a link to a new table (I called it uniques). Now, you have a record for โ€œAbraham Lincolnโ€ that has 1 linked record (no duplicates found), and you have a record for โ€œJohn Smithโ€ with 2 linked records (duplicates found).

In the โ€œuniquesโ€ table, create a rollup of the autonumber field, using the โ€œMINโ€ formula. So, for John Smith, this field will now contain 1, and for Abraham Lincoln it will contain 3.

Back in the main table, create a lookup field, using the link to โ€œuniquesโ€ and the rollup field you created within uniques.

Now (again the main table), create a formula, which will look something like this:
IF(LOOKUP_FIELD<AUTONUMBER_FIELD,โ€œDeleteโ€,โ€œDo Not Deleteโ€)

Now, the first John Smith and the Abraham Lincoln record will show โ€œDo not Deleteโ€, but the second John Smith will show โ€œDeleteโ€.

Iโ€™m sure that you could use an โ€œarrayโ€ type rollup to attempt to merge fields in the duplicate records (or least to see if there are multiple or conflicting entries in certain fields).

David_Smedberg
6 - Interface Innovator
6 - Interface Innovator

[copy and pasted from the other thread - Row Duplication Detection]

Hi all! I found a workaround to help find and delete duplicate records. As is, this only works for deleting perfect duplicates, it does NOT merge records together into a single record.

Imagine a table with 3 records: John Smith, John Smith, and Abraham Lincoln.

First, create an auto number field.

Next, ensure you have a field that contains the same text for each duplicate record (โ€œJohn Smithโ€) but is unique for records that do are not duplicates (โ€œAbraham Lincolnโ€).

Next, turn this field into a link to a new table (I called it uniques). Now, you have a record for โ€œAbraham Lincolnโ€ that has 1 linked record (no duplicates found), and you have a record for โ€œJohn Smithโ€ with 2 linked records (duplicates found).

In the โ€œuniquesโ€ table, create a rollup of the autonumber field, using the โ€œMINโ€ formula. So, for John Smith, this field will now contain 1, and for Abraham Lincoln it will show 3.

Back in the main table, create a lookup field on the link to โ€œuniquesโ€ and lookup for the rollup field you created.

Now, create a formula, which will look something like this:
IF(LOOKUP_FIELD<AUTONUMBER_FIELD,โ€œDeleteโ€,โ€œDo Not Deleteโ€)

Now, the first John Smith record will show โ€œDo not Deleteโ€, but the second John Smith will show โ€œDeleteโ€.

Cari_Adamek
4 - Data Explorer
4 - Data Explorer

+1 Iโ€™m looking for a way to delete and/or merge duplicates as well!

Andrew_Enright
9 - Sun
9 - Sun

OMG, please. In an agile workplace leveraging ad-hoc tools, Airtable evangelists are often asked to ingest and โ€œAirtableโ€ (upgrade) spreadsheets of dubious quality. The time it takes to cleanse such data far exceeds the the effort to make the imported work truly relational (and useful). For now, we rely on Excel/Numbers to rework files before uploading them to Airtable. This isnโ€™t fast.

Darren_Cleary
4 - Data Explorer
4 - Data Explorer

I have 5767 records that need the duplicates deleted, this feature would save me so much time, please add it ASAP

Desmond_B
4 - Data Explorer
4 - Data Explorer

Wait, we canโ€™t find and delete duplicate rowsโ€ฆ Well, Sticking with google sheets for now then. I have thousands of data i would like to import and some maybe duplicates. I canโ€™t imagine a excel like tool that doesnโ€™t have this feature.

Kenny_Curry
4 - Data Explorer
4 - Data Explorer

Chiming in here as a request to add duplicate detection rules, ability to expose UIDs to make importing related records easier, and also a simple merge feature would be a tremendous help.

Thanks for the awesome work. I just discovered Airtable and couldnโ€™t be more excited about it!

-Kenny

Alix_Deschamps
4 - Data Explorer
4 - Data Explorer

Would love duplicated row detection too! Letโ€™s do it Airtable team :slightly_smiling_face: