Compare ranges for exactness

  • Thread starter Thread starter JeffFinnan
  • Start date Start date
J

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
 
Simply use an equality function. Let's say you want to
compare range A1:D10 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.
 
Mike,

Thanks,
Jeff
Simply use an equality function. Let's say you want to
compare range A1:D10 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.
Click to expand...
 
There is no built in VBA function to do it.

You could use an array formula


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


Range("C1:D10").Value = Range("A1:B10").Value
? Evaluate("=NOT(OR(A1:B10<>C1:D10))")
True
 
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
Click to expand...

*************************************************************************
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
 
Back
Top