Alerts on duplicate values

We are assigning a unique identifier across 3 separate sheets. The unique identifier creation is a manual process at this stage as this is a short term problem we are solving for. Is there an automation that we can use to notify if there are two of the same values for different lines? We have a report pulling in the data to one place but wasn't sure how to run an automation across multiple sheets.

Best Answer

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Your basic find the duplicates formula is something like this:

    =COUNTIF(ColumnName:ColumnName, ColumnName@row)

    You can work this cross-sheet as well:

    =COUNTIF({ColumnRange in other sheet}, ColumnName@row)

    So if you want to get the counts of your unique identifier from two other sheets, just add the local version to the two cross-sheet versions. If you get a value greater than 3, then there's a duplicate in one of the sheets.

    =COUNTIF(ColumnName:ColumnName, ColumnName@row) + COUNTIF({ColumnRange in cross-sheet 1}, ColumnName@row) + COUNTIF({ColumnRange in cross-sheet 2}, ColumnName@row)

    Set your automation so that if the count changes to greater than 3, send the alert.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

Answers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

    Your basic find the duplicates formula is something like this:

    =COUNTIF(ColumnName:ColumnName, ColumnName@row)

    You can work this cross-sheet as well:

    =COUNTIF({ColumnRange in other sheet}, ColumnName@row)

    So if you want to get the counts of your unique identifier from two other sheets, just add the local version to the two cross-sheet versions. If you get a value greater than 3, then there's a duplicate in one of the sheets.

    =COUNTIF(ColumnName:ColumnName, ColumnName@row) + COUNTIF({ColumnRange in cross-sheet 1}, ColumnName@row) + COUNTIF({ColumnRange in cross-sheet 2}, ColumnName@row)

    Set your automation so that if the count changes to greater than 3, send the alert.

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Emily McNeeley
    Emily McNeeley ✭✭✭✭✭
  • Andrea Palmer
    Andrea Palmer ✭✭✭✭

    This was exactly what I needed! Thanks!