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

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:

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.

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.

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

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: