Highlight duplicates that share the same date

0
votes

I am trying to highlight duplicate addresses that share the same date.

enter image description here

I have tried these formulas in Conditional Formatting but they did not work.

=IF(SUMPRODUCT(--(A:A=A1),-(B:B=B1))=-1,"No Duplicate","Duplicate") =COUNTIFS($A$1:$A$21,A1,$A$1:$A$21,"<>",$B$1:$B$21,B1,$B$1:$B$21,"<>")>1

If you could also explain what the formula is doing would be awesome.

Thanks a lot.

2
excelexcel-formulaconditional-formatting

2 Answers

1
votes

Use the 2nd formula but change $A$1 to $A$2, $B$1 to $B$2, A1 to $A1 and B1 to $B1.

=COUNTIFS($A$2:$A$21,$A2,$A$2:$A$21,"<>",$B$2:$B$21,$B2,$B$2:$B$21,"<>")>1
1
votes

Another option:

=SUM(($A2=$A$2:$A$10)*($B2=$B$2:$B$10))-1

enter image description here