ONLY detect duplicates in a column

I tried the already existing topic about duplicates but was unable to find what I am looking for.
So basically, I have a list of applicants listed chronologically according to the day they applied, and each of the applicants has their own unique ID number. What I need is to be able to see automatically if someone with the same ID applies…I don’t need to remove duplicates from the list, nor to regroup them or anything. I just need them to be marked somehow in case the same ID occurs, so that I can now immediately if someone is applying again or for the first time.
I google sheets I use the =countif(A:A,A1)>1 …is there an equivalent in airtable?

Thanks and sorry if this was already asked.

Hi there,

This can get complicated pretty quickly, but the approach I would take is this:

  • Link each applicant to a single record on a new summary table (or existing if you have this already). Depending on how you load the applicant data, you may be able to include the link to the summary table automatically for incoming data (i.e. through Zapier).
  • Rollup all of the unique ID numbers into a new field on the summary table with the formula ARRAYJOIN(values)
  • Create another rollup or lookup field on the table with your applicants to pull the above rollup field into each record on your applicant table, let’s say you call it, {All IDs}
  • Last, create a formula field on the applicant table with the formula:

(LEN({All IDs}) - LEN(SUBSTITUTE({All IDs},{Applicant ID},"")))/LEN({Applicant ID})

The above formula should give you the amount of times the {Applicant ID} appears in the rollup field of {All IDs} :slight_smile:

As @Neads_Admin mentions, this may seem complicated at first. You may want to look at the Quickstart section to the guide I reference in this post.

I would simplify the previous instructions slightly by using an aggregation formula in the rollup field; if you have a large number of applicants, it should also speed up processing a little:

  1. The first step she mentions is essential: Link every record of your [Applicants] table to a single record in a new [Summary] table. You can find several ways to do this here; chose whichever one seems simplest and most logical to you.
  2. To quote the previous reply, for step 2, "Rollup all of the unique ID numbers into a new field in the [Summary] table with the formula ARRAYJOIN(values)" Call this field {All IDs}.
  3. Define a new rollup field in your [Applicants] table called something like {Number of Applications}. It should (a) follow the link to [Summary] and rollup {All IDs} using the following aggregation formula (basically, copy-and-paste this formula into the place where you would ordinarily enter an aggregation function.):
(LEN(
    values
    )-LEN(
        SUBSTITUTE(
            values,
            {Applicant ID},
            ''
            )
        )
    )/LEN(
        {Applicant ID}
        )

This is essentially the same formula @Neads_Admin provided, but it uses one fewer field and it doesn’t require keeping a copy of the entire {All IDs} field in each record — which seems to accelerate processing somewhat.

2 Likes

Thank you for this! I wouldn’t have thought to use the formula in the rollup field itself - Brilliant!