Compare two arrays
 

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

Compare two arrays
 

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157541

Microsoft Office Help

Compare two arrays
 

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

--
Rick (MVP - Excel)


"joel" wrote in message
...

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


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=157541

Microsoft Office Help

Compare two arrays
 

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 if


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157541

Microsoft Office Help

Compare two arrays
 

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 Snidow wrote:
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

Compare two arrays
 

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

"joel" wrote:


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 if


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=157541

Microsoft Office Help

.

Compare two arrays
 

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" wrote:

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 Snidow wrote:
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
.

Compare two arrays
 

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 Snidow wrote:
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" wrote:

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 Snidow wrote:
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
.