Alerts on duplicate values

Emily McNeeley

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.

Jeff Reisman

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.

Jeff Reisman

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.

Emily McNeeley

Thanks Jeff!

Andrea Palmer

This was exactly what I needed! Thanks!