Google-sheets – Highlight duplicate based on two columns

conditional formattinggoogle sheets

I have a spreadsheet of patient data (several thousand records) where I need a way to indicate whether there's a duplicate patient. We know there's a duplicate if the last name and the date of birth are the same, which are in two separate columns. Does anyone know how to use conditional formatting to highlight either the row or those cells when there's a duplicate in two columns? This is a sample of what the data look like (fake data):

Best Answer

I believe the following forula will work for you:

=AND(C1<>"",COUNTIF(ARRAYFORMULA($C:$C&$E:$E),index(ARRAYFORMULA($C:$C&$E:$E),ROW($C1),))>1)