compare values

I

Ivor Williams

I have a sheet with many rows of numbers. The numbers in each column are
supposed to be identical. Is there some way to verify that all the numbers
are identical without manually scanning them?

Ivor
 
J

JulieD

Hi Ivor

if by identical you mean
A1=B1
then in C1 simply type
=IF(A1= B1,"","ERROR")
and fill down (double click on + at bottom right corner of the cell)
- you can then check out the "error" ones

if, however, you mean the numbers are somewhere in the first column and
somewhere in the second column but not necessarily next to each other, use
the array formula (this means enter with control & shift & enter not just
enter) from Chip Pearson's site (http://www.cpearson.com/excel/duplicat.htm)

=IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")

(enter into cell C1 - change $A$10 to the cell reference of your last
number), with control & shift & enter and then fill down)

Hope this helps
Cheers
JulieD
 
M

Mike H

Nice solution and explanation, JulieD :)

if by identical you mean
A1=B1
then in C1 simply type
=IF(A1= B1,"","ERROR")
and fill down (double click on + at bottom right corner of the cell)
- you can then check out the "error" ones

if, however, you mean the numbers are somewhere in the first column and
somewhere in the second column but not necessarily next to each other, use
the array formula (this means enter with control & shift & enter not just
enter) from Chip Pearson's site (http://www.cpearson.com/excel/duplicat.htm)

=IF(COUNTIF($A$1:$A$10,B1)=0,B1,"")

(enter into cell C1 - change $A$10 to the cell reference of your last
number), with control & shift & enter and then fill down)
Click to expand...
 
K

Ken Wright

Opt1) Data / Filter / Autofilter - any more than 1 value in the filter list
is immediately obvious and allows you to go straight to it.

Opt2) With data in say B2:B1000, in say B1 =COUNTIF(B2:B1000,"<>"&B1) and
copy across. Anything not 0 denotes column with problem
 
Top