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.
匹配需要是A列和B列,所以很明显The Cure不会匹配,因为两首不同的歌曲是有效的。与加里努曼/恐惧工厂相同,因为他们都可以有一首名为“汽车”的歌曲(更不用说那个是另一首的封面)。但是应该强调Faith No More的两个史诗实例。
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!
1 个解决方案
#1
3
You can use COUNTIFS for this:
您可以使用COUNTIFS:
=COUNTIFS($A:$A,$A2,$B:$B,$B2)>1
#1
3
You can use COUNTIFS for this:
您可以使用COUNTIFS:
=COUNTIFS($A:$A,$A2,$B:$B,$B2)>1