ONLY detect duplicates in a column

[asja_fejzic]

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.

[Justin_Barrett]

Could you share some screenshots of what you’re setting up? My gut says there’s a hiccup somewhere in the configuration.

[Janene_Pappas]

Hi all, I’m attempting to set this up and somewhere I’ve not got something right. @Vikas_Vimal and @W_Vann_Hall do you see where I’ve gone wrong here?

[Jon_Eynon]

I am stuck in this same space as @Janene_Pappas I have done all the steps but still show a #Error on my Rollup. Did you ever figure this out Janene? In my case I am trying to find if we have the same Applicant ID.@Justin

[Justin_Barrett]

It’s kind of surprising that I actually caught this because I wasn’t tagged correctly (it should be @Justin_Barrett, not @Justin ).

Anyway, the first issue that I see is with your {All IDs} formula field in the [Summary] table. That field should be a rollup pulling in the {Applicant ID} values from the [Applicants] table using the ARRAYJOIN(values) aggregation formula. Your current set up just mashes together the primary field values of the linked records, which won’t give you the results that you need.

However, even with that issue fixed, this probably won’t work well because the applicant IDs aren’t a consistent length. For example, the ID 12 can be found inside all of the longer IDs. Even if the other issues were fixed, it would report more duplicates than actually exist.

If the actual IDs that you plan on using are all the same length, this won’t be an issue. Otherwise you’ll need to pad those numbers so that you have a consistent length. First determine the longest ID’s length, or the longest ID that you feel would ever be used. In this case I’ll go with 8 characters, and pad extra zeroes onto the front of the ID with this formula in a field named {Padded ID}:

RIGHT(REPT("0", 8) & {Applicant ID}, 8)

With that done, change the rollup in the [Summary] table to pull those padded IDs instead of the original IDs.

The final issue is with the {Number of Applications} rollup field aggregation formula. It’s possible that older versions of Airtable would automatically convert a single-item string array into a string, but the current version does not. This is the ultimate reason behind the error message: values is an array, but the LEN() function is only designed to operate on a string, so it’s throwing an error because it doesn’t know what to do with an array. Thankfully the fix is pretty easy, and it doesn’t require an extra formula field as @Vikas_Vimal mentioned above. All that’s needed is to take each reference to values in that aggregation formula and turn it into values & "". The final aggregation formula becomes this:

(LEN(values & "") - LEN(SUBSTITUTE(values & "", {Padded ID}, ""))) / LEN({Padded ID})

With that, the count of repeated IDs works.