How can alert if I am repeating a value in a particular column

Ana Merino

I would need to be alerted if I repeat an ID value in a column. How can I do that?

Paul Newcome

You would need to insert a helper column (can be hidden after setup) that will flag duplicates.

=IF(COUNTIFS(ID:ID, @cell = ID@row) > 1, 1)

Then you would either leave the column visible, use conditional formatting, or set up an automation based on this column being flagged.

Ana Merino

Many thanks Paul, I have tried but something went wrong, it appears #UNPARSEABLE (The column formula syntax isn't quite right). I have used the formula you mention

=IF(COUNTIFS(ID:ID, @cell = ID@row) > 1, 1)

There is not any other simpler comand to be alerted when you repeat a value in a column??

Many thanks in advance for your help.

Bassam Khalil

@Ana Merino 

please check if your helper column is checkbox type or symbol with flag. if you use text/number column type then @Paul Newcome formula will give this error #UNPARSEABLE

Paul Newcome

@Bassam Khalil My formula would not generate that error if it is in a text/number column. It would simply output the number 1.

@Ana Merino Can you provide a screenshot of the formula in the sheet similar to the screenshot below?

Paul Newcome

@Ana Merino Also make sure that the column name in the formula is exactly the same as the column you are wanting to reference in your sheet.

Ana Merino

@Paul Newcome , many thanks for you help.

I am attaching a screenshot, I don know if I am using the formula in a good way, I am novel with t his.

I have replace ID by the name of the column. The thing is that I want to be alerted when a Equipment ID value is repeated.

Many thanks for your support.

Paul Newcome

Whenever you are referencing a column name that has spaces, numbers, and/or special characters, you have to wrap it in square brackets.

[Equipment ID]

[Equipment ID]:[Equipment ID]

Ana Merino

Got it!! many thanks for your help @Paul Newcome

Best,

Paul Newcome

Happy to help. 👍️