突出显示重复项,忽略同一行 [英] Highlight duplicates, ignoring same row
问题描述
位置0()1()2()3()
/ pre>
花园Tim 3 Pete 1 Oscar 1 Lucy 2
Room1 Lucy 1 Tim 1 Lucy 5 Anna 1
厨房Frank 1 Frank 2 Frank 1 Lucy 1
我想要实现的是突出显示(使用条件格式化,我可以使用替代方法)每个条目也出现在另一行。所以基本上它应该突出显示重复,但忽略重复在同一行。第一行和第一列将被排除在操作之外(没有什么大不了,我只是不选择它们),以及参数列(这是一个很大的事情,因为这几乎打破了我尝试过的所有内容,包括给出第一个答案)。我可以通过名称条目访问整个有意义的数据区域(包含名称的所有单元格)以及给定行中名称为row的所有有意义的条目。
在上面的例子中,所有
Tim
和Lucy
条目应突出显示,因为它们有在其他行中重复。Pete
,Oscar
和Anna
是独一无二的,没有突出显示。Frank
,虽然有重复,但只有它们在同一行,没有其他行包含Frank
,所以他不应该被突出显示。 Excel的自己的高亮重复项将突出显示Frank
,同时正确处理所有其他人。
如何修改条件格式的行为忽略同一行中的重复项
以下公式(感谢@Dave)导致了一个
#VALUE!
错误:=(COUNTIF(条目; B2)-COUNTIF(行; B2))> 0
解决方案或者你可以做(不需要一个IF()在条件格式配方框中使用:
= COUNTIF($ B $ 2:$ I $ 4; $ B2)> COUNTIF($ B2:$ I2; $ B2)
此单一公式应防止参数被突出显示
- 选择B2:I2和
- 将这(完全)放在条件格式框中:
= AND(NOT(ISNUMBER(B2)); COUNTIF($ B $ 2:$ I $ 4; B2)> COUNTIF($ B2:$ I2; B2))
I have a worksheet containing names in 2 dimensions. Each row represents a general location, every other column represents a specific slot in that location (each location has the same number of available slots), alternating with a parameter belonging to that name. There is a name in each cell. Here's a simplified version to show what my data looks like:
Location 0 ( ) 1 ( ) 2 ( ) 3 ( )
Garden Tim 3 Pete 1 Oscar 1 Lucy 2
Room1 Lucy 1 Tim 1 Lucy 5 Anna 1
Kitchen Frank 1 Frank 2 Frank 1 Lucy 1
What I want to achieve is to highlight (using conditional formatting, I'm open to alternative methods though) each entry that also appears in another row. So basically it should highlight duplicates, but ignore duplicates in the same row. The first row and column are to be excluded from the operation (no big deal, I just don't select them), as are the parameter columns (this is a big deal, as this pretty much breaks everything I've tried including the first answers given). I have access to the entire meaningful data area (all cells containing names) by the name "entries" and all meaningful entries in a given row by the name "row".
In my example above, all Tim
and Lucy
entries should be highlighted because they have duplicates in other rows. Pete
, Oscar
and Anna
are unique, so they're not highlighted. Frank
, while having duplicates, only has them in the same row, no other row contains Frank
, so he should not be highlighted. Excel's own highlight duplicates would highlight Frank
, while handling all the others correctly.
How can I modify the conditional formatting's behaviour to ignore duplicates in the same row?
The following formula (thanks to @Dave) resulted in a #VALUE!
error:
=(COUNTIF(entries;B2)-COUNTIF(row;B2))>0
or you could just do (no need for an IF() when used in Conditional Formatting Formula box:
=COUNTIF($B$2:$I$4;$B2)>COUNTIF($B2:$I2;$B2)
This single formula should prevent the parameters from being highlighted
- select B2:I2 and
- put this (exactly) in the conditional formatting box:
=AND(NOT(ISNUMBER(B2));COUNTIF($B$2:$I$4;B2)>COUNTIF($B2:$I2;B2))
这篇关于突出显示重复项,忽略同一行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!