compare numbers -- recursive?
 

I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

compare numbers -- recursive?
 

It's probably easier to find how many _different_ numbers there are
in the list and compare that number to 4 - X. That would then
generalise easily to X equal out of Y.


wrote:
I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

compare numbers -- recursive?
 

As there are only 4 numbers to compare you might as well forget about loops,
recursive etc. and just hard code it:

Function AnyXEqual(x As Integer, _
int1 As Integer, _
int2 As Integer, _
int3 As Integer, _
int4 As Integer) As Boolean

Dim n As Byte

n = 1

If int1 = int2 Then
n = n + 1
End If

If int1 = int3 Then
n = n + 1
End If

If int1 = int4 Then
n = n + 1
End If

If int2 = int3 Then
n = n + 1
End If

If int2 = int4 Then
n = n + 1
End If

If int3 = int4 Then
n = n + 1
End If

If n = x Then
AnyXEqual = True
End If

End Function


RBS

wrote in message
ups.com...
I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

compare numbers -- recursive?
 

Another looping method, but pretty specific to your situation:

Option Explicit
Function AnyXEqual(x As Long, int1 As Long, _
int2 As Long, int3 As Long, int4 As Long) As Boolean

Dim iCtr As Long
Dim jCtr As Long
Dim NumMatches As Long
Dim myArr(1 To 4) As Long

myArr(1) = int1
myArr(2) = int2
myArr(3) = int3
myArr(4) = int4

AnyXEqual = False
For iCtr = LBound(myArr) To UBound(myArr)
NumMatches = 0
For jCtr = LBound(myArr) To UBound(myArr)
If myArr(iCtr) = myArr(jCtr) Then
NumMatches = NumMatches + 1
End If
Next jCtr
If NumMatches = x Then
'found one, stop looking for more
AnyXEqual = True
Exit Function
End If
Next iCtr

End Function


wrote:

I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

--

Dave Peterson

compare numbers -- recursive?
 

Public Function AnyXEqual(x As Integer, int1 As Integer, _
int2 As Integer, int3 As Integer, int4 As Integer) As Boolean
Dim arr(1 To 4) As Long
Dim j As Long
Dim l As Long
Dim m As Long
Dim c As Long

AnyXEqual = False
arr(1) = int1
arr(2) = int2
arr(3) = int3
arr(4) = int4
' sort it
For j = 1 To 4
For m = 1 To 4
If arr(j) < arr(m) Then
l = arr(j)
arr(j) = arr(m)
arr(m) = l
End If
Next
Next
c = 1
For l = 1 To 3
If arr(l) = arr(l + 1) Then
c = c + 1
End If
Next
If c = x Then AnyXEqual = True
End Function

Sub test000987()
MsgBox AnyXEqual(3, 7, 2, 2, 2)
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

compare numbers -- recursive?
 

Thanks everyone. Interesting idea to count how many *different*
numbers there are. Thanks for the code examples. Eventually, I would
want to be able to compare any number of numbers (3 numbers, 4 numbers,
.... 10 numbers), which is why I thought recursive was the way to go.

compare numbers -- recursive?
 

Hmm..

If c = x Then AnyXEqual = True

for at least x matches

Helmut Weber

compare numbers -- recursive?
 

Hi,

with _any_ numbers,
you would need a function with _any_ arguments.

Hard to do.

Or you pass an excel.range to the function


--
Greetings from Bavaria, Germany

Helmut Weber

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"

compare numbers -- recursive?
 

One way for the differences:

Option Explicit
Function Uniques(ParamArray NumList() As Variant) As Long

Dim myElement As Variant
Dim myCollection As Collection

Set myCollection = New Collection

On Error Resume Next
For Each myElement In NumList()
myCollection.Add Item:=myElement, key:=CStr(myElement)
Next myElement
On Error GoTo 0

Uniques = myCollection.Count

End Function

And

Function AnyXEqual(x As Long, ParamArray NumList() As Variant) As Boolean

Dim iCtr As Long
Dim jCtr As Long
Dim NumMatches As Long

AnyXEqual = False
For iCtr = LBound(NumList) To UBound(NumList)
NumMatches = 0
For jCtr = LBound(NumList) To UBound(NumList)
If NumList(iCtr) = NumList(jCtr) Then
NumMatches = NumMatches + 1
End If
Next jCtr
If NumMatches = x Then
'found one, stop looking for more
AnyXEqual = True
Exit Function
End If
Next iCtr

End Function

Adding Helmut's sort routine may make it work quicker when you get a larger set
of numbers.



wrote:

Thanks everyone. Interesting idea to count how many *different*
numbers there are. Thanks for the code examples. Eventually, I would
want to be able to compare any number of numbers (3 numbers, 4 numbers,
... 10 numbers), which is why I thought recursive was the way to go.

--

Dave Peterson

compare numbers -- recursive?
 

If the number of numbers to compare can be any I would do it like this:

Function AnyXEqual2(X, arr As Variant) As Boolean

Dim i As Long
Dim coll As Collection

Set coll = New Collection

On Error Resume Next
For i = LBound(arr) To UBound(arr)
coll.Add vbNull, CStr(arr(i))
Next

If (UBound(arr) + (2 - LBound(arr))) - coll.Count = X Then
AnyXEqual2 = True
End If

End Function

arr is an 0-based or 1-based 1-D array of the numbers to check.


RBS

wrote in message
ups.com...
I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

compare numbers -- recursive?
 

If you are dealing with very large numbers to compare you
could speed it up by getting out of the loop if you know the function will
return True:

Function AnyXEqual2(X As Long, arr As Variant) As Boolean

Dim i As Long
Dim coll As Collection

Set coll = New Collection

On Error Resume Next

For i = LBound(arr) To UBound(arr)
coll.Add vbNull, CStr(arr(i))
If (i + (2 - LBound(arr))) - coll.Count = X Then
AnyXEqual2 = True
Exit For
End If
Next

On Error GoTo 0

End Function

Similarly, you could get out early if you know the result will be False, but
of course all this
checking takes time as well, so it usually won't be worth it.

RBS


"RB Smissaert" wrote in message
...
If the number of numbers to compare can be any I would do it like this:

Function AnyXEqual2(X, arr As Variant) As Boolean

Dim i As Long
Dim coll As Collection

Set coll = New Collection

On Error Resume Next
For i = LBound(arr) To UBound(arr)
coll.Add vbNull, CStr(arr(i))
Next

If (UBound(arr) + (2 - LBound(arr))) - coll.Count = X Then
AnyXEqual2 = True
End If

End Function

arr is an 0-based or 1-based 1-D array of the numbers to check.


RBS

wrote in message
ups.com...
I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

compare numbers -- recursive?
 

Just to be different...

Function AnyXEqual(n, ParamArray v() As Variant) As Boolean
AnyXEqual = (UBound(Filter(v, WorksheetFunction.Mode(v), True)) + 1) =
n
End Function

Sub testit()
Debug.Print AnyXEqual(3, 67, 50, 67, 98)
Debug.Print AnyXEqual(2, 67, 50, 67, 98)
Debug.Print AnyXEqual(4, 67, 50, 67, 67)
Debug.Print AnyXEqual(3, 67, 50, 67, 67)
Debug.Print AnyXEqual(2, 67, 50, 67, 67)
End Sub

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


wrote in message
ups.com...
I'm trying to create a function that will tell me if X numbers out of 4
are equal. Something like:

Function AnyXEqual(x As Integer, int1 As Integer, int2 As Integer, int3
As Integer, int4 As Integer) As Boolean
'[code]
End Function

So

AnyXEqual(3, 67, 50, 67, 98) = False
AnyXEqual(2, 67, 50, 67, 98) = True

AnyXEqual(4, 67, 50, 67, 67) = False
AnyXEqual(3, 67, 50, 67, 67) = True
AnyXEqual(2, 67, 50, 67, 67) = True

I thought about using a For i = 1 to x loop (or 2) to compare them, but
I think that would only work if x was 2 ... if x was 3 I would need a
nested loop, and if x was 4 I would need another nested loop.

Something tells me this is a perfect situation for a recursive
function, but my brain has trouble thinking on that level. Any ideas?
Thanks.

compare numbers -- recursive?
 

You never cease to amaze me, Dana!

Only after reading your solution and looking up Filter in help did I vaguely
recall reading about it when it was first introduced. But, otherwise, I
didn't even know of its existence.

Do note that since the Filter function works on strings (sub-strings to be
exact), this is not a general purpose solution. For example,
Debug.Print AnyXEqual(3, 67, 50, 67, 167) returns true

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Just to be different...

Function AnyXEqual(n, ParamArray v() As Variant) As Boolean
AnyXEqual = (UBound(Filter(v, WorksheetFunction.Mode(v), True)) + 1) =
n
End Function

Sub testit()
Debug.Print AnyXEqual(3, 67, 50, 67, 98)
Debug.Print AnyXEqual(2, 67, 50, 67, 98)
Debug.Print AnyXEqual(4, 67, 50, 67, 67)
Debug.Print AnyXEqual(3, 67, 50, 67, 67)
Debug.Print AnyXEqual(2, 67, 50, 67, 67)
End Sub

compare numbers -- recursive?
 

the Filter function works on sub-strings ...

Thanks Tushar! You're right. I can't believe I still fall for that one.
:(

What I started out trying to do was to find out which number occurred the
most (Mode).
I was trying to get a version of "CountIf" to work, but of course it won't.

Function AnyXEqual(n, ParamArray v() As Variant) As Boolean
Dim M
M = WorksheetFunction.Mode(v)
' Next line won't work of course...
Debug.Print WorksheetFunction.CountIf(v, M)
'End Function

The only non-looping solution that I know of would be something like the
following. However, it's probably faster to just loop like the other
solutions.

Function AnyXEqual(n, ParamArray v() As Variant) As Boolean
Dim A ' (A)rray
ActiveWorkbook.Names.Add "T_", v
A = Filter(["?" & T_ & "?"], "?" & WorksheetFunction.Mode(v) & "?")
AnyXEqual = (UBound(A) + 1) = n
End Function


Here's an attempt at a looping solution.

Function AnyXEqual(n, ParamArray v() As Variant) As Boolean
Dim j As Long
Dim c As Long ' (C)ounter
Dim M
M = WorksheetFunction.Mode(v)
Do While j <= UBound(v) And c < n
c = c - (v(j) = M)
j = j + 1
Loop
AnyXEqual = (c = n)
End Function

Thanks for the catch!! :)
--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"Tushar Mehta" wrote in message
om...
You never cease to amaze me, Dana!

Only after reading your solution and looking up Filter in help did I
vaguely
recall reading about it when it was first introduced. But, otherwise, I
didn't even know of its existence.

Do note that since the Filter function works on strings (sub-strings to be
exact), this is not a general purpose solution. For example,
Debug.Print AnyXEqual(3, 67, 50, 67, 167) returns true

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
Just to be different...

Function AnyXEqual(n, ParamArray v() As Variant) As Boolean
AnyXEqual = (UBound(Filter(v, WorksheetFunction.Mode(v), True)) + 1)
=
n
End Function

Sub testit()
Debug.Print AnyXEqual(3, 67, 50, 67, 98)
Debug.Print AnyXEqual(2, 67, 50, 67, 98)
Debug.Print AnyXEqual(4, 67, 50, 67, 67)
Debug.Print AnyXEqual(3, 67, 50, 67, 67)
Debug.Print AnyXEqual(2, 67, 50, 67, 67)
End Sub

compare numbers -- recursive?
 

you've seen solutions using the collection. Its even easier using the
scripting Dictioanry object. This, unlike a collection, allows one to test if
a key already exists or not.

In the example below, I add each of the pass parameters to the dictionary.
If it doesn't already exist as a key, I add it, setting th evalue to 1. If
the value is already in the dictionary's key, I increment the value by 1,
then test if it matches the 'x' value.

In the IDE set a reference to the Microsoft Scripting Runtime DLL the add
the function below...

Option Explicit
Function AnyXEqual(count As Long, values As Range) As Boolean
Dim index As Long
Dim val As Variant
Dim sVal As String
Dim dic As Scripting.Dictionary
Set dic = New Scripting.Dictionary
For Each val In values
sVal = CStr(val)
If dic.Exists(sVal) Then
dic.Item(sVal) = dic.Item(sVal) + 1
If dic.Item(sVal) = count Then
AnyXEqual = True
Exit Function
End If
Else
dic.Add sVal, 1
End If
Next
Set dic = Nothing
End Function

Note: one any value set the function value to TRUE, we don't need to test
any more, so the code exits the function
The default for a boolean is FALSE...some purists might say it should be
explicitly set, but not me, thats what defaults are for. But it does make
debugging easier. Add it if you want
AnyXEqual = FALSE
just before the End Function OR just after the DIM statements at the start.

"Helmut Weber" wrote:

Public Function AnyXEqual(x As Integer, int1 As Integer, _
int2 As Integer, int3 As Integer, int4 As Integer) As Boolean
Dim arr(1 To 4) As Long
Dim j As Long
Dim l As Long
Dim m As Long
Dim c As Long

AnyXEqual = False
arr(1) = int1
arr(2) = int2
arr(3) = int3
arr(4) = int4
' sort it
For j = 1 To 4
For m = 1 To 4
If arr(j) < arr(m) Then
l = arr(j)
arr(j) = arr(m)
arr(m) = l
End If
Next
Next
c = 1
For l = 1 To 3
If arr(l) = arr(l + 1) Then
c = c + 1
End If
Next
If c = x Then AnyXEqual = True
End Function

Sub test000987()
MsgBox AnyXEqual(3, 7, 2, 2, 2)
End Sub

--
Greetings from Bavaria, Germany

Helmut Weber, MVP WordVBA

Win XP, Office 2003
"red.sys" & Chr$(64) & "t-online.de"