Search code examples
excelconditional-formatting

Highlight duplicates based on 2 different columns

let's say that's my excel data:

PersNr Surname Travel Type Departure date Trip ID
578123 Smith Abroad trip 03.05.2023 1
578123 Smith Abroad trip 04.05.2023 2
574754 Jones Abroad trip 22.05.2023 3
574754 Jones Dom. trip 22.05.2023 4
541255 Brown Abroad trip 28.05.2023 5
543241 Abrams Abroad trip 28.05.2023 6

What I want to achieve is go through each row and highlight rows when it detects duplicate based on PersNr and Departure date. I want to highlight every person that has more than 1 trip on the same day (the best would be if it would also take into consideration that travel type must differ, but that's not important). Built-in duplicate detection tool would only search based on 1 column. I tried some tricks with conditional formatting, but no result. I have over 4000 rows and it would save me a lot of time.

/edit added trip id column

Solution

  • Use a rule based on COUNTIFS. The "<>"&$C2 checks if the Travel Type is different.

    =COUNTIFS($A:$A,$A2,$D:$D,$D2,$C:$C,"<>"&$C2)

    applied to