Problem with Conditional Formatting for Duplicates in Multiple Column Worksheet

[alcharbonneau]

Good Morning


I have been trying to use conditional formatting (CF) in Excel 2007 worksheet that has 10 columns and 220 lines of numbers. The goal is to identify those numbers that occur in all ten columns.


I have tried CF for duplicates and unique values with no success.


Is this an appropriate use of CF; are there alternative approaches to identifying the duplicate numbers that occur in all ten columns.


Thanks


Al

 

[NARAYANK991]

Hi Al ,


This link seems to provide an answer involving the MOREFUNC add-in ( it's free ) :


http://www.excelforum.com/excel-worksheet-functions/753289-identifying-duplicates-across-multiple-columns.html


Narayan

 

[bhasoriya]

=COUNTIFS(A:J,O16)


A to J is your 10 columns

O16 is your criteria


can also use in next sheet and filter it..

 

[Luke M]

Lengthy, but this formula would flag cells where the value appears in all 10 columns.


=AND(COUNTIF($A:$A,A2),COUNTIF($B:$B,A2),COUNTIF($C:$C,A2),COUNTIF($D:$D,A2),

COUNTIF($E:$E,A2),COUNTIF($F:$F,A2),COUNTIF($G:$G,A2),COUNTIF($H:$H,A2),

COUNTIF($I:$I,A2),COUNTIF($J:$J,A2))

 

[Haseeb A]

Also,


Select A2, then apply this formula & choose the format


=SUMPRODUCT(SIGN(COUNTIF(OFFSET($A$2,,COLUMN($A$2:$J$2)-COLUMN($A$2),220),A2)))=10


Then in Conditional Formatting window 'Applies to' change to =$A$2:$J$220


Regards,

Haseeb

 

[Xenophon]

How about this:


=FREQUENCY(A2:J2,A2:J2)=COLUMNS(A2:J2)


..which returns TRUE or FALSE


If all the cells contain the same number, then the first element of the bins array will contain a count which will be the same as the number of columns.

 

[Xenophon]

or this:


=SUMPRODUCT((1/COUNTIF(A2:J2,A2:J2)))=1


=SUMPRODUCT((1/COUNTIF(A2:J2,A2:J2))) on its own produces the number of occurrences of each unique number in the range. The "=1" at then produces a TRUE or FALSE result.

 

[alcharbonneau]

Thank you one and all. Will try suggestions in the morning.


Again, thank you.


Al