How can I count the number of duplicate records in my table?

[Burner]

Hi,

I have a table that contains the first and last names of people and what I’d like to do is to have a field beside this that counts the number of times a first name + last name shows up in the table. This is because it is possible that I can have multiple entries for the same person and I eventually want to extract unique users for use in another table. The first thing I need to do is to identify duplicates, but I’m struggling with this.

My approach was to concatenate & lower case the first and last name to get a standardized format to check for duplication. Would appreciate any help on figuring out how to find duplicates in the table. Also, it’s not enough that I just know if a record is a duplicate or not, I need to know how many times it shows up in the table, so some sort of count formula but I can’t seem to figure this out.

(names are not real)

Any help would be much appreciated. Thanks so much.

[ScottWorld · Accepted solution]

There is one trick you can use to accomplish this:

To start, duplicate your concatenated formula field.

Then, take that duplicated field and convert it from a formula field into a “linked record” field, and link it to a new table.

That new table will only give you ONE record for each concatenated name, BUT each record will link back to all the related records in your original table with that same concatenated name.

So, in that new table, you can create a new field of type “Count” (that’s one of Airtable’s field types) which will count the number of linked records in your original table.

Then, back in your original table, you can create a lookup field which looks up this “Count” field.

However, the problem here is that this will only work for existing records that you have already created in your system.

For future records, you’ll need to create an automation that automatically updates the “Linked Record” field (in your original table) with the concatenated formula value, whenever the concatenated formula changes.

Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question. :slightly_smiling_face:

[Burner]

Thanks a ton, Scott. Really appreciate the detailed instructions. I’m definitely going to try this out and let you know it turns out.

Once again, really appreciate it.

[ScottWorld]

You’re welcome! Also, I couldn’t tell from your original description, but make sure that your concatenated field is a formula field… that’s the trick to making this work for new records in the future.

[Burner]

Hi Scott. Sorry I didn’t get back sooner. I just tried out your solution and it worked brilliantly. Thank you so much because there are couple of super gems in the solution you outlined that I would have never seen and that totally deserve to be highlighted for others to leverage as well.

1. Duplicating the concatenated field, converting it to a Linked Record in a new table. Adding a Count field in the new table and creating a lookup back to this Count in the original table. Brilliant.

2. After that, I would have never realized that there’d need to be an automation to keep linking new incoming records because the trick in step 1, will work only for existing records and not for new records. I created a couple of automations using Airtable’s native automation functionality that did the trick. The first was to create a linked record in the main table when any new record is created. The second automation was to manage modifications. So that if a First or Last name were modified such that the concatenated full name now became a duplicate (and wasn’t earlier), the automation would check for when the concatenated full name was modified and do a count again.

Thanks once again for this brilliant solution.

:grinning:

[ScottWorld]

Hi @Burner!

You’re welcome, and that’s great to hear! I’m so happy that you were able to successfully implement this — with a few great tweaks, too! :grinning_face_with_big_eyes: :raised_hands:

Best,
Scott

[pencil]

I cannot follow your step 2. It does not do it. Can you elaborate this a bit more, please?

"The first was to create a linked record in the main table when any new record is created. The second automation was to manage modifications."

Thank you so much!