突出显示不使用条件格式相互重复的复制 [英] Highlight Duplicates Not Next To Each Other Using Conditional Formatting

问题描述

我们以一定的顺序在Excel中列出了一些零件。由于我不会进入的原因，我们需要强调什么时候有重复的不是彼此相邻的。目前，我以条件格式使用此公式来完成这项工作。

= AND（COUNTIF（$ A $ 2：$ A $ 82 ，$ A2）> 1，$ A1 <$ $ A2，$ A2 $ $ A3）

在下面的示例中有一对重复的对象，我们希望FO-1694被突出显示，因为它们不是彼此相邻。但是我们不希望NIS0257被突出显示，因为它们是。

目前

理想情况下，这是我们想要看到的...

预期

可以使用条件格式化来实现任何想法？

_{注意：我已经从SuperUser论坛上的过去的帖子直接发布了}

如果更改范围请确保注意绝对和相对的标记。

We have a list of parts in Excel in a certain order. For reasons I won't get into, we need to highlight when there are duplicates that aren't next to each other. Currently, I'm using this formula in a conditional format to do the job.

=AND(COUNTIF($A$2:$A$82,$A2)>1,$A1<>$A2,$A2<>$A3)

This mostly works well except in cases where there are pairs of duplicates like in the example below, we would want FO-1694 to be highlighted, because they aren't all next to each other. But we would not want NIS0257 to be highlighted because they are.

Currently

Ideally, this is what we want to see...

Expected

Any ideas on this could be achieved using conditional formatting?

_{Note: I have directly posted this from a past post on the SuperUser Forum (Link) as I am in need of this EXACT conditional formatting for the EXACT same circumstances. I do not take credit for the construction of this post, but only hope to obtain an answer for a question that previously went unanswered.}

解决方案

Use this formula:

=NOT(AND(A2=OFFSET(A2,COUNTIF($A2:$A$82,A2)-1,0),A2=OFFSET(A2,-(COUNTIF($A$2:$A2,A2)-1),0)))

If changing the range then make sure to pay attention to what is marked as absolute and relative.

这篇关于突出显示不使用条件格式相互重复的复制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！