compare values in 2 ranges
 

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

compare values in 2 ranges
 

"Martin Wheeler" wrote:
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 he http://support.microsoft.com/?kbid=213818

compare values in 2 ranges
 

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

"Shawn O'Donnell" wrote in message
...
"Martin Wheeler" wrote:
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 he http://support.microsoft.com/?kbid=213818

compare values in 2 ranges
 

Shawn O'Donnell wrote:
"Martin Wheeler" wrote:

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

compare values in 2 ranges
 

"Alan Beban" wrote:
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.

compare values in 2 ranges
 

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
"Alan Beban" wrote in message
...
Shawn O'Donnell wrote:
"Martin Wheeler" wrote:

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

compare values in 2 ranges
 

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

--
Regards,
Tom Ogilvy


"Martin Wheeler" wrote in message
...
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

compare values in 2 ranges
 

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

--
Regards,
Tom Ogilvy

compare values in 2 ranges
 

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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

compare values in 2 ranges
 

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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy

compare values in 2 ranges
 

Just a quick thank you. I have the code working and it is great.
Thanks a lot Tom
Ta,
Martin
"Martin Wheeler" wrote in message
...
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

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy