compare values

  • Thread starter Thread starter Ivor Williams
  • Start date Start date
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
 
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
 
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...
 
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
 
Back
Top