complicated conditional formatting for duplicates

[jimrward]

in simple terms I have one to many columns of data, for simplicity lets use columns A, B, C

A, B, C
297, 295, 293
296, 295
297, 295, 292
294

now it is easy enough to highlight dupes in column A with COUNTIF() > 1

what i would like to do is highlight cell in Column B, which is associated as already being a duplicate in column A

so in the example above 297 would be highlighted in A2 and A4
and I would like only B2 and B4 highlighted in B, as these are associated as dupes, and NOT B3 as it is not a duplicate in A

so the formula needs to be logically connected to the countif in A

 

[Norie]

Why not just select columns A and B and use a formula like this:

=COUNTIF($A:$A, $A1)>1

 

[jimrward]

ok that highlights entries in column A which are dupes, but it also highlights the cells in column be which are not dupes

the row with the dupe in A needs to check B to see if this is also a dupe in column B

the scenario is for a competition results

if there are any ties in column A, then highlight, so that I know to add extra criteria in column B, if still a tie in Column B, then add further set of criteria.
in highlighting ties I only need to add extra where required as opposed to doing it for all the results

 

[rspecianjr]

Hey guys,

What about countifs?

=COUNTIFS($A:$A, $A1,$B:$B, $B1)>1

Hope this helps,

Robert D. Specian Jr.

 

[jimrward]

afraid that did not work robert, no dupes detected at all

 

[Expiry]

In col D =COUNTIF(A:A,A1)>1.
In Col E =COUNTIF(B:B,B1)>1.

Conditional Formatting for A1 should be Formula is =D1=TRUE
Conditional Formatting for B1 should be Formula is =(AND(D1=TRUE,E1=TRUE))

That won't distinguish if the duplicates in B are on the corresponding rows to thos in A. ie. these are ALL duplicates, but no row is the same:

297 206
258 248
297 248
258 206

 

[jimrward]

all items on one row relate to each other I have added my own helper column where I concatenate A and B and use the countif on this for column B

=COUNTIF($D:$D, $D1)>1 where D contains

297295
296295
297295
294

column A I just use countif