CODEWITHSUNDEEP
excelconditional-formatting
Steve G.
Steve G.

Reputation: 409

Excel Conditional Formatting: Highlight Duplicates When More than One Row Matches

In a song database, I'm trying to highlight duplicate songs. In my table, I have three columns: artist name, song title, and filename. I need to create a conditional formatting rule where all duplicates are highlighted when both the artist name and the song title are the same. For instance, here's my data:

Artist name (col A)          Song title (B)          Filename (C)
===========                  ==========              ========
Gary Numan                   Cars                    Gary Numan - Cars.mp3
Faith No More                Epic                    Faith No More - Epic.mp3
The Cure                     Love Cats               The Cure - Love Cats.mp3
The Cure                     Friday I'm in Love      The Cure - Friday I'm in Love.mp3
Fear Factory                 Cars                    Fear Factory - Cars.mp3
Faith No More                Epic                    Faith No More - Epic.mp3

The match needs to be both column A and column B, so obviously The Cure won't match because two different songs is valid. Same with Gary Numan/Fear Factory, since they can both have a song named "Cars" (never mind that one is a cover of the other). But Faith No More's two Epic instances should be highlighted.

I know I need to select "Use a formula to determine which cells to format" while in the Conditional Formatting options, but what should I enter into the formula field? Everything I've tried has come up with results I don't want.

Thanks, all!

Upvotes: 0

Views: 175

Answers (1)

BigBen
BigBen

Reputation: 50162

You can use COUNTIFS for this:

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

enter image description here

Upvotes: 3

Related Questions