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.

[AlliAlosa]

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} :slightly_smiling_face:

[W_Vann_Hall]

As @AlliAlosa 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 @AlliAlosa 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.

[AlliAlosa]

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

[Vikas_Vimal]

The answer above is Brilliant. Note that with some strings, you will have to convert to a text string or the formula LEN(Value) Fails. Create a new column next to the rolled up value - Concatenate(Value,"") and use this as column in the main Formula.

[Ahmed_Elagami]

Thank you guys so much for this brilliant idea, @AlliAlosa @W_Vann_Hall @Vikas_Vimal. I followed your instructions and it’s working like a charm, wouldn’t work without the concatenate formula thing, The Only thing that I can’t automate now is how to create and add the Newly created records link to the Calc field. so they are included in the Len calculations , Can you please just guide me on how to implement this?

Thank you so much for your support.

[AlliAlosa]

Hi there @Ahmed_Elagami! So glad you’ve got it working!

I would suggest setting up a new automation, triggered “when a record matches conditions”.

Set the conditions to be where your linked record field which links to your summary table is empty.

Set the action step to be “update record”, and update the same record which triggered the automation. Choose your linked record field as the field to update and insert the name of your summary record.

When on, this automation should catch any records that aren’t linked appropriately and add them to your summary field.

I hope this helps!

[Ahmed_Elagami]

Hello again @AlliAlosa, Thankyou so much for the instructions, I just followed your steps in here, I just added( ‘.’ ) to each empty cell in the Linked records field through the automation, so the rollup is updated right away. I can even see the green rectangle update sign in all the rollup lookup fields. It’s working like a charm now, I can’t thank you enough, Merry Christmas, Happy new year.

[Megan_Smith1]

Thanks so much for this! Is it possible to post a quick example in airtable that can be viewed? Looks like an elegant solution, but I’m having trouble getting it to work as described.

[Maximilian_Brec]

Hello all - I’ve been trying to implement, and have gotten to the Concatenate step.

However, when I try and create the “All IDs” lookup field back in the Applicants sheet, it says “unexpected error” and doesn’t let me create the field.

Anyone else have this issue?