使用条件格式（Larger Dataset）突出显示不相邻的副本 [英] Highlight duplicates not next to each other using conditional formatting (Larger Dataset)

问题描述

我们以一定的顺序在Excel中列出产品编号。因为我不会进入的原因，我们需要强调什么时候有重复的对象彼此不相邻。目前，我使用这个公式以条件格式来完成这个工作。

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

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

目前

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

预期

我最初在同一个问题上问了1月30日（。

解决方案

我应该想到这些问题：

  = NOT（AND（IFERROR（COUNTIF（OFFSET（A1,0,0，-COUNTIF（$ A $ 1：$ A1，A2）），A2），0）= IFERROR（COUNTIF（$ A $ 1：$ A1，A2） ，0），IFERROR（COUNTIF（OFFSET（A3,0,0，COUNTIF（$ A3：$ A $ 5422，A2）），A2），0）= IFERROR（COUNTIF（$ A3：$ A $ 5422，A2），0 ），
  

应该这样做。

We have a list of product numbers 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

I originally asked this same question January 30th (link) and I received what seemed to be a working solution with the conditional formatting formula

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

While this original answer appears to work for a small group of data (example), it does not function as needed on much larger datasets like the ones I'll be dealing with (images below are of the issues with the larger dataset). I've adjusted the formula as necessary to accommodate the larger range, so the conditional formatting formula you see in the larger dataset pictures & file is

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

Cells with the arrow pointing at them should be highlighted

For the last picture, even though it's the end of the range the cells should still be highlighted since they're blank and I need all blank cells in the range to be highlighted too.

The full file with the larger dataset can be downloaded from my dropbox account using this link.

解决方案

I should have thought of those problems:

=NOT(AND(IFERROR(COUNTIF(OFFSET(A1,0,0,-COUNTIF($A$1:$A1,A2)),A2),0)=IFERROR(COUNTIF($A$1:$A1,A2),0),IFERROR(COUNTIF(OFFSET(A3,0,0,COUNTIF($A3:$A$5422,A2)),A2),0)=IFERROR(COUNTIF($A3:$A$5422,A2),0),A2<>""))

Should do it.

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