Conditionally formatting duplicate rows in google sheets

Question

I want to apply conditional formatting so that all the rows which match another row exactly are highlighted.

Let's say I have a spreadsheet like the following

|   | a | b | c |
|---|---|---|---|
| 1 | A | B | C | // Matches row 3 and 6
| 2 | A | B | A | // Matches row 5
| 3 | A | B | C | // Matches row 1 and 6
| 4 | B | B | C | // Matches no other row
| 5 | A | B | A | // Matches Row 2
| 6 | A | B | C | // Matches row 1 and 3
| 7 | B | B | A | // Matches no other row

All the rows except for row 4 and 7 would be highlighted.

For to rows to be considered duplicates, the value of each/every cell in a given row must exactly match the value of the corresponding cell (cell in the same column) in a duplicate row.

My attempt so far can only return the values of rows with only the first 2 cells being duplicate and returns the concatenation of all the duplicate values in each row, which is very far away from what I want.

CC = arrayformula(A:A&" "&B:B&" "&C:C) returns a new row which is the concatenation of A, B, and C, which is coercing the cell values into strings so "1" and 1 which are not the same appear to be the same, and also doesn't work across the entire row (could do If I just kept adding Columns, but would look terrible).

=filter(unique(CC), arrayformula(countif(CC, unique(CC)) > 1)) CC is the returned value from the previous equation

This would output A B C A B A

Then I could add a conditional formatting rule with a custom formula that Highlights a row if it's concatenated contents "Match" one of the return values from the previous equation, but I don't know how to do that, and the previous equation is already pretty flawed.

Ideally I want a solution that involves no string concatenation or entering in all column names.

This is not the same as this question or this question even though they have almost the exact same title. I had to change highlighting in my question title to Conditionally formatting because of a duplicate titled question. — Ryan White

Liora Haydont Liora Haydont · Accepted Answer · 2018-04-25T19:52:48

Let's go over what is needed to create this function.

1st you need to get the rows as a string to be able to compare them like you did. I didn't use space like you did because it takes place, but you can keep them.

=ARRAYFORMULA(A:A&B:B&C:C)

The issue with that is that since the formula will be on 3 column, we don't want it to become C:C&D:D&E:E so we have to fix the column.

=ARRAYFORMULA($A:$A&$B:$B&$C:$C)

Yay! Now we have a list of string that represent the "value" of each row. We can now count for each line how many times they are found. I used A2 cause I guess you have a header, but if you don't, simply replace it with A1.

=COUNTIF(ARRAYFORMULA($A:$A&$B:$B&$C:$C);A2&B2&C2)

We also have to fix the column here or the function will only work on the 1st one.

=COUNTIF(ARRAYFORMULA($A:$A&$B:$B&$C:$C);$A2&$B2&$C2)

And now all that's left is check if you want to see thoses who are unique or thoses who have matches

=COUNTIF(ARRAYFORMULA($A:$A&$B:$B&$C:$C);$A2&$B2&$C2)>1

Conditionally formatting duplicate rows in google sheets

3 Answers