Highlight duplicates not next to each other using

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.

回答1:

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.

回答2:

I'm not sure if this would work for you

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

I've tested it on a test bit of data, and seems to work. You need to check for the 1st row, as you cant check the previous cell, so the A1<>A2 part..

1 being true condition on my test so would need tweaking for conditional formatting. Enjoy.