Compare two arrays

[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

 

[joel]

You could use the join statement to make them both a string and then
compare

Str1 = join(Array1,",")
Str2 = join(Array2,",")
if Str1 = Str2 then

 

[Rick Rothstein]

The Join function will only work on one-dimensional VB arrays, not two
dimensional ranges (even if converted to VB arrays).

 

[joel]

You could do this

Matched = True
for i = 1 to 4
Str1 = join(Array1(i),",")
Str2 = join(Array2(i),",")
if Str1 <> Str2 then
Matched = False
Exit For
end if
next i

If Matched = false then
msgbox("Arrays did'nt Match")
end i

 

[Dana DeLouis]

Can this be done other than comparing each

individual field one at a time?

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 = [A14]
y = [A69]
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 Snidow]

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

 

[Greg Snidow]

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

Can this be done other than comparing each
individual field one at a time?

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 = [A14]
y = [A69]
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

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

.

 

[Dana DeLouis]

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

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

Can this be done other than comparing each
individual field one at a time?

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 = [A14]
y = [A69]
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

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

.