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.

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.


bodhi2.71828@gmail.com 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.

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

<bodhi2.71828@gmail.com> wrote in message
news:1138570099.539410.96060@o13g2000cwo.googlegroups.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.
>

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


bodhi2.71828@gmail.com 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

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"

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.

Hmm..

If c >= x Then AnyXEqual = True

for at least x matches

Helmut Weber

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"

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.



bodhi2.71828@gmail.com 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

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

<bodhi2.71828@gmail.com> wrote in message
news:1138570099.539410.96060@o13g2000cwo.googlegroups.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.
>

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" <bartsmissaert@blueyonder.co.uk> wrote in message
news:%23zCa12SJGHA.2628@TK2MSFTNGP15.phx.gbl...
> 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
>
> <bodhi2.71828@gmail.com> wrote in message
> news:1138570099.539410.96060@o13g2000cwo.googlegroups.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.
>>
>

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


<bodhi2.71828@gmail.com> wrote in message
news:1138570099.539410.96060@o13g2000cwo.googlegroups.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.
>

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 <#1gSuJTJGHA.2040@TK2MSFTNGP14.phx.gbl>, ddelouis@bellsouth.net
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
>
>

>> 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" <tmUnderscore200310@tushar-mehta.SeeOhEm> wrote in message
news:MPG.1e4736cd28a361b098b31e@msnews.microsoft.com...
> 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 <#1gSuJTJGHA.2040@TK2MSFTNGP14.phx.gbl>, ddelouis@bellsouth.net
> 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
>>
>>

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