Compare two arrays

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
 
J

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
 
R

Rick Rothstein

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

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
 
D

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 = [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
 
G

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
 
G

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

Dana DeLouis said:
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 = [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
.
 
D

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

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?

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
.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top