Highlighting duplicate rows based on 2 columns

Question

I have the following data:

Title                                         | Volume | Price
---------------------------------------------------------------
Girl Friends Omnibus                          | 1      | 9.99
Girl Friends Omnibus                          | 2      | 9.99
Kisses, Sighs and Cherry Blossom Pink Omnibus | 1      | 9.99
Hana & Hina After School                      | 1      | 5.99
Hana & Hina After School                      | 2      | 8.99
Girl Friends Omnibus                          | 2      | 8.99
Girl Friends Omnibus                          | 3      | 9.99

If I wanted to use conditional formatting to highlight duplicates I'd use the "Format Unique or Duplicate Values" conditional formatting rule. However if I did that it would look for duplicates of just one row, so for the first column only Kisses, Sighs and Cherry Blossom Pink Omnibus would not be highlighted.

What I want, however, is for the highlighting to only occur if the first two columns are not unique. So only Girl Friends Omnibus Volume 2 should be highlighted. The price should not factor at all. In a sense Title and Volume serve to create a composite Primary Key if they were in a database.

When I try to look this up I get variations of this which highlights values in one column if they exist in another. This won't work for me since the example data shows that the two aren't comparable.

Answer 1

This can be done simply by using the COUNTIFS() function:

Select the appropriate cells in the table, making sure that A2 is the active cell, and enter the following for the conditional formatting formula:

=COUNTIFS($A$2:$A$8,$A2,$B$2:$B$8,$B2)>1

While this works for the simple cases, more advanced situations might require a different technique using SUMPRODUCT(). It would be a good idea to learn how to use this more advanced technique. (I always use instead of using COUNTIFS().)

The more advanced equivalent to the above formula would be:

=SUMPRODUCT(($A$2:$A$8=$A2)*($B$2:$B$8=$B2))>1

Answer 2

This issue can be solved by use of Helper Column also.

Follow these steps:

1. In Cell E238 write this formula: =A238&B238 & Fill it down.
2. Select A238:B244.
3. From Home Tab Click, Conditional Formatting.
4. From the poped up menu click, New Rule.
5. Then From the Rule Type click, Use Formula To Determine Which Cells to Format.
6. In Format Values Where This Formula Is True enter this formula: =COUNTIF($E$238:$E$244,$E238)>1
7. Then hit Format Button & apply an appropriate color format and press Ok & on the next screen finish with Ok.

You get both duplicate rows will be highlighted as you can find also in the attached screen shot.

N.B. You can adjust the Cell addres in both formulas according to your need.