Find duplicates

[NichoD]

Hello,

I have different columns with date. All data in column O are sorted ID-numbers, while column c are dates. I want to first check i duplicates exists in colmn O, and if it does check if column C, containing dates differ.

For example; If O4, O5 and O6 all have ID-number 12345. C4 is 2023-05-20, C5 2023-05-20 and C6 2023-05-22, this should be alerted.

If O7 and O8 have ID-number 5678 and C7 and C8 is 2023-06-01, nothing is alerted.

How should this be performed?

Kind Regards.

 

[dalvin647]

Hi NichoD,

Try this formula:
=IF(COUNTIF(O:O,O4)>COUNTIFS(O:O,O4,C:C,C4),"Alert","Good")

Logic as follows, count how many same IDs there are, then count that to how many same IDs+Date there are. If less, then there's a differing date for the same ID.

Thanks.

Book1
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
3	Date												ID-numbers
4	2023-05-20												12345	Alert
5	2023-05-20												12345	Alert
6	2023-05-22												12345	Alert
7	2023-06-01												5678	Good
8	2023-06-01												5678	Good
9	2023-07-01												1	Good
10	2023-07-01												2	Good
Sheet1

Cell Formulas
Range	Formula
P4:P10	P4	=IF(COUNTIF(O:O,O4)>COUNTIFS(O:O,O4,C:C,C4),"Alert","Good")

 

[Fluff]

Another option

=IF(COUNTIFS(O:O,O2,C:C,"<>"&C2),"Alert","Ok")

 

[NichoD]

Thank you so much! Worked like a charm!

 

[Fluff]

Glad we could help & thanks for the feedback.