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).

--
Rick (MVP - Excel)


"joel" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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
>

> 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 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

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
>
> .
>

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

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