Highlight cell if duplicate, but not 2 duplicates next to each other

Dutch_girl

New Member
Joined
Jul 6, 2021
Messages
3
Office Version
  1. 365
Platform
  1. Windows
I have a column where I need to highlight duplicate numbers but only when they are not next to each other:

11
57
48
45
12<-- this should not be highlighted
12<-- this should not be highlighted
95
87
12<-- this should be highlighted
43

I'm filling in data regarding contracts and contract numbers. 1 contract can have multiple lines, but should only be in the file once. The multiple lines per contract can vary.

Can someone please help me?
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Hi Dutch_girl,

Does this do what you ask?

Dutch_girl.xlsx
A
1Numbers
211
357
448
545
612
712
895
987
1012
1143
12
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A999Expression=AND(COUNTIF(A:A,A2)>1,A2<>A3,A2<>A1)textNO
 
Upvote 0
Hi,
Here is one way by using a helper column

Book3
AB
1
211FALSE
357FALSE
448FALSE
545FALSE
612FALSE
712FALSE
895FALSE
987FALSE
1012TRUE
1143FALSE
Sheet1
Cell Formulas
RangeFormula
B2:B11B2=IF(COUNTIF($A$2:$A$11,A2)>1,IF(AND(A2<>A1,A2<>A3),TRUE,FALSE))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B11Cell Valuecontains "true"textNO
 
Upvote 0
Hi Dutch_girl,

Does this do what you ask?

Dutch_girl.xlsx
A
1Numbers
211
357
448
545
612
712
895
987
1012
1143
12
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A2:A999Expression=AND(COUNTIF(A:A,A2)>1,A2<>A3,A2<>A1)textNO
Hi Toadstool,
This is indeed what I need, but I can't get it to work. Could you help to explain a bit more?
Thanks!
 
Upvote 0
Hi,
Here is one way by using a helper column

Book3
AB
1
211FALSE
357FALSE
448FALSE
545FALSE
612FALSE
712FALSE
895FALSE
987FALSE
1012TRUE
1143FALSE
Sheet1
Cell Formulas
RangeFormula
B2:B11B2=IF(COUNTIF($A$2:$A$11,A2)>1,IF(AND(A2<>A1,A2<>A3),TRUE,FALSE))
Cells with Conditional Formatting
CellConditionCell FormatStop If True
B2:B11Cell Valuecontains "true"textNO
[/RANGE
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Hi Hrayani, I prefer not to have an extra column, but if I can't get it to work any other way, I'll use it and keep the column hidden. Thanks!
 
Upvote 0
Cells with Conditional Formatting
CellConditionCell FormatStop If True
Hi Hrayani, I prefer not to have an extra column, but if I can't get it to work any other way, I'll use it and keep the column hidden. Thanks!
Thanks...

You can use that formula in conditional formatting provided by Toadstool in post # 2
 
Upvote 0
Hi Toadstool,
This is indeed what I need, but I can't get it to work. Could you help to explain a bit more?
Thanks!
OK, let me try.

Effectively you are saying "Highlight a cell if it has duplicates but the duplicate is not in an adjacent row".

So the AND says all three conditions must be true.
=AND(...)

There must be more than 1 occurrence of this value in the column.
COUNTIF(A:A,A2)>1

The value on the next row must not equal the value on this row
A2<>A3

The value on the previous row must not equal the value on this row
=AND(COUNTIF(A:A,A2)>1,A2<>A3,A2<>A1)

To apply the Conditional Format you highlight from A2 down as far as you need and enter the formula like this
1625569437691.png



NOTE: One important aspect is that all the row numbers are relative so effectively when the Conditional Format gets to row 10 it actually is
Excel Formula:
=AND(COUNTIF(A:A,A10)>1,A10<>A11,A10<>A9)
...so the COUNTIF returns 3 because there's 3 number 12s in the column, A10 is not equal to A11 and A10 is not equal to A9 so with AND all conditions are TRUE so it returns TRUE and the Conditional Format highlights the cell with the Format you selected.
 
Upvote 0
Solution

Forum statistics

Threads
1,213,549
Messages
6,114,264
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top