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.
Best Answer
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: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 usingCOUNTIFS()
.)The more advanced equivalent to the above formula would be: