Compare two arrays

  • Thread starter Thread starter Greg Snidow
  • Start date Start date
G

Greg Snidow

Greetings. Lets say I have two arrays, array1 and array2, both of the same
dimensions, say four columns and four rows. Is there a way to easily compare
them? For example, I want to do If array1.value = array2.value. Can this be
done other than comparing each individual field one at a time? Thank you.

Greg
 
The Join function will only work on one-dimensional VB arrays, not two
dimensional ranges (even if converted to VB arrays).
 
Can this be done other than comparing each
individual field one at a time?
Click to expand...

Not the best solution, but if the arrays are numbers, and nonsingular,
maybe generate an identify matrix, and make sure it sums to 4.
This has no error checking.

Sub Demo()
Dim x, y
Dim B As Boolean
x = [A1:D4]
y = [A6:D9]
Debug.Print ArrayEqual(x, y)
End Sub

Function ArrayEqual(x, y) As Boolean
Dim UL
UL = UBound(x, 1)
With WorksheetFunction
ArrayEqual = Equal(.Sum(.MMult(x, .MInverse(y))), UL)
End With
End Function

Function Equal(x, y) As Boolean
Dim d As Double
d = 0.0000000000001 '1E-13
Equal = Abs(x - y) <= d
End Function

= = = = = = = = =
Again, just one of a few ideas.
Dana DeLouis
 
Joel, thanks for the input. I could not get the Join function to work with
my arrays at all, and the VB help for Join is seriously lacking. Anyhow, I
took what I think is your basic idea, and accomplished it like this....

For r = 1 to RowCount Step 1
For c = 1 to ColumnCount Step 1
String1 = String1 & MyArray(r,c)
Next c
Next r

It seems to concatanate all the array values into one string, so I think I
am good to go. Thanks again.

Greg
 
Dana, thanks for the input. I have not yet used functions in Excel, so I am
not sure exactly what your code is doing. I think I have a solution, but I
am going to try to figure out what your code does. In the mean time, thanks
again.

Greg

Dana DeLouis said:
Can this be done other than comparing each
individual field one at a time?
Click to expand...

Not the best solution, but if the arrays are numbers, and nonsingular,
maybe generate an identify matrix, and make sure it sums to 4.
This has no error checking.

Sub Demo()
Dim x, y
Dim B As Boolean
x = [A1:D4]
y = [A6:D9]
Debug.Print ArrayEqual(x, y)
End Sub

Function ArrayEqual(x, y) As Boolean
Dim UL
UL = UBound(x, 1)
With WorksheetFunction
ArrayEqual = Equal(.Sum(.MMult(x, .MInverse(y))), UL)
End With
End Function

Function Equal(x, y) As Boolean
Dim d As Double
d = 0.0000000000001 '1E-13
Equal = Abs(x - y) <= d
End Function

= = = = = = = = =
Again, just one of a few ideas.
Dana DeLouis


Greg said:
Greetings. Lets say I have two arrays, array1 and array2, both of the same
dimensions, say four columns and four rows. Is there a way to easily compare
them? For example, I want to do If array1.value = array2.value. Can this be
done other than comparing each individual field one at a time? Thank you.

Greg
Click to expand...
.
Click to expand...
 
Yeah. Forget that idea, as there are would be situations where it would
give a false answer.

On a worksheet, this array formula might work with
the two name areas 'x and 'y
This appears to work with both Text and Numbers.

=(SUM(--(x=y)-1))=0

= = = = = =
HTH :>)
Dana DeLouis

Greg said:
Dana, thanks for the input. I have not yet used functions in Excel, so I am
not sure exactly what your code is doing. I think I have a solution, but I
am going to try to figure out what your code does. In the mean time, thanks
again.

Greg

Dana DeLouis said:
Can this be done other than comparing each
individual field one at a time?
Click to expand...

Not the best solution, but if the arrays are numbers, and nonsingular,
maybe generate an identify matrix, and make sure it sums to 4.
This has no error checking.

Sub Demo()
Dim x, y
Dim B As Boolean
x = [A1:D4]
y = [A6:D9]
Debug.Print ArrayEqual(x, y)
End Sub

Function ArrayEqual(x, y) As Boolean
Dim UL
UL = UBound(x, 1)
With WorksheetFunction
ArrayEqual = Equal(.Sum(.MMult(x, .MInverse(y))), UL)
End With
End Function

Function Equal(x, y) As Boolean
Dim d As Double
d = 0.0000000000001 '1E-13
Equal = Abs(x - y) <= d
End Function

= = = = = = = = =
Again, just one of a few ideas.
Dana DeLouis


Greg said:
Greetings. Lets say I have two arrays, array1 and array2, both of the same
dimensions, say four columns and four rows. Is there a way to easily compare
them? For example, I want to do If array1.value = array2.value. Can this be
done other than comparing each individual field one at a time? Thank you.

Greg
Click to expand...
.
Click to expand...
Click to expand...
 
Back
Top