I am trying to setup a notification for when an ID is used twice within a column.

I am trying to setup a notification for when an ID is used twice within a column. I have more than 100 ID within the sheet and the sheet is still being populated so using the condition if equal to isn't quite efficient because it will mean I have to go into the automation to check the ID each time a new one is added. Is there any other way around please

Best Answer

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    What about adding an ID check column and using the following formula to count how many times the id's name is in the field. Then set up an automation for when the count is greater than 1 to fire. Just replace "ID column name with the name of your actual ID column. Remove the brackets if there is no space or if the column name ends in a number. 😊

    =COUNTIF([ID column name]:[ID Column Name], [ID Column Name]@row)

    This should give you a clear count of all the times that particular row's ID is used elsewhere in the column.

    Then your automation should check any time anything is added or updated and the condition for firing should be that the ID Check column is greater than 1.

    Would that work?

Answers

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭
    Answer ✓

    What about adding an ID check column and using the following formula to count how many times the id's name is in the field. Then set up an automation for when the count is greater than 1 to fire. Just replace "ID column name with the name of your actual ID column. Remove the brackets if there is no space or if the column name ends in a number. 😊

    =COUNTIF([ID column name]:[ID Column Name], [ID Column Name]@row)

    This should give you a clear count of all the times that particular row's ID is used elsewhere in the column.

    Then your automation should check any time anything is added or updated and the condition for firing should be that the ID Check column is greater than 1.

    Would that work?

  • Thanks. I will try this.

  • Mike Wilday
    Mike Wilday ✭✭✭✭✭✭

    Awesome. Let me know if you have any trouble. Or new questions arise! :)