Find duplicate, but only if an adjacent cell is identical
 

There is plenty of advice across the internet if you have some records
in a sheet, and want to find out if a value in a cell (a reference
number, for example) is duplicated anywhere else in that column. The
commonly used formula is =COUNTIF($B:$B,B1)1, used in conjunction
with the Conditional Formatting feature.

However, is there a way I can engage the conditional formatting only
if that cell is duplicated *and* the cell next to it in that row is
the same as the cell next to the other duplicate entry? So for
instance, if a reference number is identical, and the next column has
a month in it (eg November), the conditional formatting only engages
if the number is the same, and the month is the same too? Or am I
asking too much here?

Also, in a similar vein, is there a way I can adapt the formula only
to engage conditional formatting if the next row down contains an
identical value? I tried =COUNTIF(B1,B2)1 but that doesn't work. I
must admit I'm not entirely sure I know how the first CountIf is
working - I know that $B:$B if referring to the entire B column
(absolute), but I want to only refer to the next row down. So with
relative references, I thought this second formula would work. What
am I doing wrong?

Thank you for any help you can give.

Steve Wylie

Find duplicate, but only if an adjacent cell is identical
 

On Jul 30, 5:46*pm, wrote:
There is plenty of advice across the internet if you have some records
in a sheet, and want to find out if a value in a cell (a reference
number, for example) is duplicated anywhere else in that column. *The
commonly used formula is =COUNTIF($B:$B,B1)1, used in conjunction
with the Conditional Formatting feature.

However, is there a way I can engage the conditional formatting only
if that cell is duplicated *and* the cell next to it in that row is
the same as the cell next to the other duplicate entry? So for
instance, if a reference number is identical, and the next column has
a month in it (eg November), the conditional formatting only engages
if the number is the same, and the month is the same too? *Or am I
asking too much here?

Also, in a similar vein, is there a way I can adapt the formula only
to engage conditional formatting if the next row down contains an
identical value? *I tried =COUNTIF(B1,B2)1 but that doesn't work. *I
must admit I'm not entirely sure I know how the first CountIf is
working - I know that $B:$B if referring to the entire B column
(absolute), but I want to only refer to the next row down. *So with
relative references, I thought this second formula would work. *What
am I doing wrong?

Thank you for any help you can give.

Steve Wylie

=COUNTIF(B1:B2,B1)1 Is this helpful????

Find duplicate, but only if an adjacent cell is identical
 

Ah, thank you - yes, this will work for the second part of my
question. So I'm halfway there...

Steve