Thanks for sharing the formula. I am new to SS and trying to understand the most efficient way to structure formulas. It would be very helpful if you could explain in plain prose what your formula is doing. For your formula to work the base/master sheet will have to be structured a certain way, correct? What form criteria/column is this formula using to decide whether a new form entry is a duplicate? I assume the primary criteria is in column B, right?
Thanks in advance!
Hi @Sol Elisha
There is no specific structure needed in the sheet for this formula. It's looking into different columns and counting how many times the values in the current row appear in the sheet. If it's more than one time, then this row is a Duplicate!
the unique combination of: the content of Column A in this current row and the content of Column B in this current row
Column, Criteria, Column, Criteria
ColumnB:ColumnB, [email protected], ColumnA:ColumnA, [email protected]
appears anywhere else in the sheet, COUNT how many times there's a row that matches these two criteria.
COUNTIFS(columns and criteria)
If the COUNT is >1 or greater than 1, this means there's a Duplicate, so say the word "Duplicate".
=IF(COUNTIFS(columns and criteria) > 1, "Duplicate",
If the COUNT is only 1, then this is an Original row.
=IF(COUNTIFS(columns and criteria) > 1, "Duplicate", "Original")
For a final formula:
=IF(COUNTIFS(ColumnB:ColumnB, [email protected], ColumnA:ColumnA, [email protected]) > 1, "Duplicate", "Original")
Now, if you only have one column with a unique value to check through your entire sheet, you only need to list one column and criteria:
=IF(COUNTIFS(UniqueColumn:UniqueColumn, [email protected]) > 1, "Duplicate", "Original")
The criteria is the cell in this current row (or @row) for a specific column. Then you look through that column to see if it appears anywhere else (column:column).
Does that make sense?
If you still need help, it would be useful to see a screen capture of your current sheet, but please block out any sensitive data.
©2022. All Rights Reserved Smartsheet Inc.