compare values

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


"Ivor Williams" <[email protected]> wrote in message
news:4aXMd.273812$Xk.80113@pd7tw3no...
>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
>

Nice solution and explanation, JulieD

On Sat, 5 Feb 2005 14:28:33 +0800, JulieD wrote:

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

> "Ivor Williams" <[email protected]> wrote in message
> news:4aXMd.273812$Xk.80113@pd7tw3no...
>>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?

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

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------

"Ivor Williams" <[email protected]> wrote in message
news:4aXMd.273812$Xk.80113@pd7tw3no...
> 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
>
>