Find duplicate, but only if an adjacent cell is identical

[stevewy]

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

 

[Nayab]

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????

 

[stevewy]

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

Steve