Compare ranges for exactness

[JeffFinnan]

Is there a simple way to compare ranges for exactness? Let's say there are two
ranges of an identical number of cells. One would like to see if each cell is
of one range is identical to the corresponding range of the other cell. One
does not care what the difference. One only cares as to whether the ranges in
total are exact. One could write some code to compare each cell, cell by cell,
but I was wonder if there were some function that could be called.

Thanks,
Jeff

 

[Mike]

Simply use an equality function. Let's say you want to
compare range A110 against range E1:H10 on the same
sheet. In I1 enter =A1=E1 then fill down and across to
match the size of the range. If you are working on two
different worksheets you can use the same idea, using
something like =Sheet1!A1=Sheet2!A1.

 

[JeffFinnan]

Mike,

Thanks,
Jeff

Simply use an equality function. Let's say you want to
compare range A110 against range E1:H10 on the same
sheet. In I1 enter =A1=E1 then fill down and across to
match the size of the range. If you are working on two
different worksheets you can use the same idea, using
something like =Sheet1!A1=Sheet2!A1.

 

[Tom Ogilvy]

There is no built in VBA function to do it.

You could use an array formula


? Evaluate("=NOT(OR(A1:B10<>C110))")
False


Range("C110").Value = Range("A1:B10").Value
? Evaluate("=NOT(OR(A1:B10<>C110))")
True

 

[Jiri Cihar]

Is there a simple way to compare ranges for exactness? Let's say there are two
ranges of an identical number of cells. One would like to see if each cell is
of one range is identical to the corresponding range of the other cell. One
does not care what the difference. One only cares as to whether the ranges in
total are exact. One could write some code to compare each cell, cell by cell,
but I was wonder if there were some function that could be called.

Thanks,
Jeff

*************************************************************************
Provided ranges to be compared are A1:B10 and E1:F10 you can use an array function
=MIN(SIGN(A1:B7=E1:F7)) (CTRL+SHIFT+ENTER)

This solution works for numbers, letters - but is not case sensitive.

If you need case sensitive skill, then add EXACT function

=MIN(SIGN(EXACT(A1:B7;E1:F7))) (CTRL+SHIFT+ENTER)

Please take care of small difference A1:B7=E1:F7 vs A1:B7;E1:F7

Hope this helps

Jiri Cihar
www.dataspectrum.cz