Database Duplicate entries (Date & Item Based)

CJ Dijkstra

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.

Regards

Chris

Paul Newcome

Try this...

=COUNTIFS(Item:Item, @cell = Item@row, Date:Date, @cell = Date@row)

Paul Newcome

Try this...

=COUNTIFS(Item:Item, @cell = Item@row, Date:Date, @cell = Date@row)

CJ Dijkstra

@Paul Newcome You have just made my day - thank you very much!!

Regards

Chris

Paul Newcome

Happy to help. 👍️