compare values in 2 ranges

[Martin Wheeler]

xl 2003
I need to compare the values in ranges M1:M3 to CE7:CE9. The values, in the
current worksheet, are 3,8,5 and 3,5,8. These values are the same but not in
the same order. The order is not important. What I need to know is if the
values are the same, regardless of order. So in this case the answer would
be yes and proceed to the next sub.
I could write something like :-
If .range("M1").value = .range("CE7").value or .range("M1").value =
..range("CE8").value or .range("M1").value = .range("CE9").value then....

and do this for all 3 cells in M1:M3 but it seems a clumsy, longwinded
method.

Is there a simpler method? Any help would be greatly appreciated.
Ta,
Martin

 

[Guest]

I need to compare the values in ranges M1:M3 to CE7:CE9. The values, in the
current worksheet, are 3,8,5 and 3,5,8. These values are the same but not in
the same order. The order is not important. What I need to know is if the
values are the same, regardless of order.

How about copying the ranges to arrays, sorting the arrays, then stepping
through the arrays to see if all the elements match?

Remember that when you copy a range to a Variant array, you get a
two-dimensional array. To transfer the range values to an array you do
something like this:

For i = 1 to uBound(range_A, 1)
array_A(i) = range_A(i, 1)
next i

To sort the array, use code someone else checked. You'll find code for two
different algorithms here: http://support.microsoft.com/?kbid=213818

 

[Martin Wheeler]

Hi Shawn,
Thanks for the help. I have had a look at the web page and will give it a
go.
Ta,
Martin

 

[Alan Beban]

How about copying the ranges to arrays, sorting the arrays, then stepping
through the arrays to see if all the elements match?

Remember that when you copy a range to a Variant array, you get a
two-dimensional array. To transfer the range values to an array you do
something like this:

For i = 1 to uBound(range_A, 1)
array_A(i) = range_A(i, 1)
next i

To sort the array, use code someone else checked. You'll find code for two
different algorithms here: http://support.microsoft.com/?kbid=213818

If the OP is planning to use the above approach, then to transfer the
range values to an array it is not necessary to loop through the range
or the array; you can use

arr1 = Range("M1:M3")
arr2 = Range("CE7:CE9")

And if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to the workbook, then after
the arrays are sorted one can use ColumnsEqual, which will return True
or False; the looping is built in to the function.

Alan Beban

 

[Guest]

you can use

arr1 = Range("M1:M3")
arr2 = Range("CE7:CE9")

True, but then the sort algorithm has to be modified to operate on
two-dimensional arrays.

And if the functions in the freely downloadable file at
http://home.pacbell.net/beban are available to the workbook, then after
the arrays are sorted one can use ColumnsEqual

Looks like some handy stuff. Thanks for the pointer.

 

[Martin Wheeler]

Hi Alan,
Thanks for the help. I have not used arrays so this will be a good chance.
I have downloaded your free file - thanks
Ta,
Martin

 

[Tom Ogilvy]

Sub AB()
Dim rng1 as Range, rng2 as Range
Dim res as Variant
Set rng1 = Range("M1:M3")
Set rng2 = Range("CE7:CE9")
res = Evaluate("Sum(CountIf(" & _
rng1.Address & "," & rng2.Address & "))")
if res = 3 then
' match
Else
' don't match
End if
End Sub

 

[Tom Ogilvy]

That one wouldn't handle duplicate numbers (ex: 3,3,1 and 1,2,3). This
should work better:

Sub Main()
if RangeMatch(Range("M1:M3"),Range("CE7:CE9")) = True then
' they match
End if
End Sub



Public Function RangeMatch(rng1 As Range, rng2 As Range)
Dim res1 As Long, res2 As Long
res = Evaluate("Small(Countif(" & _
rng1.Address & "," & rng2.Address & "),1)")
res1 = Evaluate("Small(Countif(" & _
rng2.Address & "," & rng1.Address & "),1)")
If res <> 0 And res1 <> 0 Then
AA = True ' match
Else
AA = False ' don't match
End If
End Function

 

[Tom Ogilvy]

whoops, I changed the function name at the last minute and didn't adjust the
code. The function should be:

Public Function RangeMatch(rng1 As Range, rng2 As Range)
Dim res1 As Long, res2 As Long
res = Evaluate("Small(Countif(" & _
rng1.Address & "," & rng2.Address & "),1)")
res1 = Evaluate("Small(Countif(" & _
rng2.Address & "," & rng1.Address & "),1)")
If res <> 0 And res1 <> 0 Then
RangeMatch = True ' match
Else
RangeMatch = False ' don't match
End If
End Function

 

[Martin Wheeler]

Hi Tom,
Thanks for the code. I will need to study it as it is a bit beyond me but am
sure it will do the job, your code always does.
Ta,
Martin

 

[Martin Wheeler]

Just a quick thank you. I have the code working and it is great.
Thanks a lot Tom
Ta,
Martin