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.
This was very helpful. Is there any way to add a clause if the field I am looking at is blank?
I have an ID field that I want to check for duplicates but many of the records don't have the ID field populated at all and those I'd like to refrain from checking.
Yes! You can add an IF statement in front of the formula, so if the ID field is blank, return blank, otherwise go through the other formula.
=IF([ID Field]@row = "", "", IF(COUNTIFS([ID Field]:[ID Field], [ID Field]@row) > 1, "Duplicate", "Original"))
Let me know if that works for you!
You could do this via the API where you can precise which rows to ignore etc.
Below is a short video in which the duplicated records are highlighted, this happens via API (hence the delay) and without any formula in the sheet.
So easy, thank you!!!
Create and edit formulas in Smartsheet
Formula combinations for cross sheet references
Smartsheet functions list
©2022. All Rights Reserved Smartsheet Inc.