Database Duplicate entries (Date & Item Based)


Hi All,

I am struggling with a Item & Date based duplication formula.

1) For ease of reference I have filtered the database on Item "WL04". Various "ITEMS" with unique id's gets submitted into this database.

2) There can only be one entry per "ITEM" per "DATE"

3) As can be seen on the screenshot there are two entries for 27/10/21 (marked in red) as well as for 28/10/21 (marked in blue).

4) I would like the "ISDUP" column to show the value "2" in both these cases since there is 2 entries on both dates. If there is only one entry per date it must show "1".

5) My current formula looks like this: =COUNT(DISTINCT(COLLECT([πŸ“„ HEADING]:[πŸ“„ HEADING], [πŸ“… DATE]:[πŸ“… DATE], [πŸ“… DATE]@row, [πŸ“„ ITEM]:[πŸ“„ ITEM], [πŸ“„ ITEM]@row)))

6) Note that the "HEADING" column is all C0177. I am not worried about this variable i.e. this can change from date to date.

I would really appreciate your help.




Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!