CODEWITHSUNDEEP
excelexcel-formulaconditional-formatting
Randal Andrade Nunes
Randal Andrade Nunes

Reputation: 4884

Highlight duplicates that share the same date

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.

Upvotes: 0

Views: 1539

Answers (2)

basic
basic

Reputation: 11968

Another option:

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

enter image description here

Upvotes: 1

norie
norie

Reputation: 9857

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

Upvotes: 1

Related Questions