Re: help require for algebra function??


"Anthony England" <aengland@xxxxxxxxxx> wrote in message
news:dthskq$in5$1@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

"Terry Kreft" <terry.kreft@xxxxxxxxx> wrote in message
news:ZUmdnSKGe-T87WHeSa8jmw@xxxxxxxxxxxxxx
My first thought is there are 20! combinations here which is (according
to
calc <g>) 405483668029439994.

So good luck with that.



--

Terry Kreft


Are you sure that's correct? Each of the elements can appear either 1 or
0 times so we've only got to check 2^20 sets.


"Norman Fritag" <muenchr@xxxxxxxxxxxxxx> wrote in message
news:43fc670f$0$12197$5a62ac22@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi there
I need some help, to matching data!
eg: out of 20 elements: eg (10,2,4 17,24,5,30, 40, 50, 100, 23, 35, 200,
3501, 201, 245, 323,2000, 33, 44,265,etc)
I would want to know which of these elements eg: make up the sum 275??
in this example the combination: 245,30 = 275 ; 200,24,50 =275;
200,30,40,5 = 275 and so forth
Would I have to use vba code function and or could I us as well sql?

any hints are much appreciated

Regards

Norman


As nobody else seems to want to have a go, here is a first attempt. It
might be possible to optimize but I suppose it depends whether current
performance is acceptable. With the example you gave, I found 103 matches
in 5.89 seconds which may be acceptable to you, but just note that I wrote
this bit of code on a whim and haven't had time to prove to myself that all
matches will be found. However, it does seem to work Comments anyone?


' *****************************
' Paste all this in a new module
' *****************************
Option Compare Database
Option Explicit

Public Sub DoTest()

On Error GoTo Err_Handler

Dim strSeries As String
Dim lngSum As Long
Dim strAllMatches As String
Dim astrSeries() As String
Dim astrNumbers() As String
Dim strTemp As String
Dim lngMatchCount As Long
Dim sngTime As Single
Dim lngX As Long
Dim lngY As Long

sngTime = Timer

strSeries = "10,2,4,17,24,5,30,40,50," & _
"100,23,35,200,3501,201," & _
"245,323,2000,33,44"

lngSum = 275

strAllMatches = GetAllMatches(lngSum, strSeries)

astrSeries() = Split(strAllMatches, ";")

For lngX = LBound(astrSeries) To UBound(astrSeries)

strTemp = CStr(lngSum) & " = "

astrNumbers = Split(astrSeries(lngX), ",")

For lngY = LBound(astrNumbers) To UBound(astrNumbers)
strTemp = strTemp & astrNumbers(lngY) & " + "
Next lngY

strTemp = Left$(strTemp, Len(strTemp) - 3)

lngMatchCount = lngMatchCount + 1

Debug.Print strTemp

Next lngX

sngTime = Timer - sngTime

MsgBox CStr(lngMatchCount) & " matches found in " & _
CStr(sngTime) & " seconds", vbInformation

Exit_Handler:
Exit Sub

Err_Handler:
MsgBox Err.Description, vbExclamation, "Error No: " & Err.Number
Resume Exit_Handler

End Sub


Private Function GetAllMatches(lngTargetSum As Long, strSeries As String) As
String

Dim strReturn As String
Dim astrValues() As String
Dim alngValues() As Long
Dim strValue As String
Dim lngCount As Long
Dim lngMax As Long

astrValues = Split(strSeries, ",")

ReDim alngValues(LBound(astrValues) To UBound(astrValues))

For lngCount = LBound(astrValues) To UBound(astrValues)
alngValues(lngCount) = CLng(astrValues(lngCount))
Next lngCount

lngMax = (2 ^ (UBound(alngValues) + 1)) - 1

For lngCount = 0 To lngMax
strSeries = GetMatches(lngCount, lngTargetSum, alngValues)
If Len(strSeries) > 0 Then
strReturn = strReturn & ";" & strSeries
End If
Next lngCount

If Len(strReturn) > 1 Then
strReturn = Mid$(strReturn, 2)
End If

GetAllMatches = strReturn

End Function

Private Function GetMatches(lngBits As Long, _
lngTarget As Long, _
aNumbers() As Long) As String

Dim lngCount As Long
Dim lngSum As Long
Dim strReturn As String

For lngCount = 0 To UBound(aNumbers)
If (2 ^ lngCount And lngBits) = 0 Then
lngSum = lngSum + aNumbers(lngCount)
If lngSum > lngTarget Then
Exit For
End If
End If
Next lngCount

If lngSum = lngTarget Then
For lngCount = 0 To UBound(aNumbers)
If (2 ^ lngCount And lngBits) = 0 Then
strReturn = strReturn & "," & CStr(aNumbers(lngCount))
End If
Next lngCount
If Len(strReturn) > 1 Then
strReturn = Mid$(strReturn, 2)
End If
End If

GetMatches = strReturn

End Function
' *************************
' Code End
' *************************



Results from debug window:
275 = 40 + 100 + 23 + 35 + 33 + 44
275 = 10 + 30 + 100 + 23 + 35 + 33 + 44
275 = 10 + 2 + 4 + 24 + 100 + 23 + 35 + 33 + 44
275 = 4 + 17 + 24 + 5 + 40 + 50 + 23 + 35 + 33 + 44
275 = 10 + 4 + 17 + 24 + 5 + 30 + 50 + 23 + 35 + 33 + 44
275 = 2 + 4 + 17 + 40 + 100 + 35 + 33 + 44
275 = 4 + 24 + 5 + 30 + 100 + 35 + 33 + 44
275 = 10 + 2 + 4 + 17 + 30 + 100 + 35 + 33 + 44
275 = 10 + 4 + 24 + 5 + 30 + 40 + 50 + 35 + 33 + 44
275 = 2 + 17 + 24 + 30 + 40 + 50 + 35 + 33 + 44
275 = 5 + 30 + 40 + 100 + 23 + 33 + 44
275 = 2 + 4 + 24 + 5 + 40 + 100 + 23 + 33 + 44
275 = 10 + 2 + 4 + 24 + 5 + 30 + 100 + 23 + 33 + 44
275 = 4 + 17 + 24 + 30 + 100 + 23 + 33 + 44
275 = 10 + 4 + 17 + 24 + 30 + 40 + 50 + 23 + 33 + 44
275 = 2 + 17 + 24 + 5 + 50 + 100 + 33 + 44
275 = 2 + 4 + 17 + 5 + 30 + 40 + 100 + 33 + 44
275 = 4 + 24 + 30 + 40 + 100 + 33 + 44
275 = 10 + 2 + 17 + 24 + 5 + 40 + 100 + 33 + 44
275 = 2 + 5 + 23 + 201 + 44
275 = 30 + 201 + 44
275 = 2 + 4 + 24 + 201 + 44
275 = 2 + 24 + 5 + 200 + 44
275 = 10 + 4 + 17 + 200 + 44
275 = 2 + 4 + 17 + 50 + 100 + 23 + 35 + 44
275 = 4 + 24 + 5 + 40 + 100 + 23 + 35 + 44
275 = 10 + 2 + 4 + 17 + 40 + 100 + 23 + 35 + 44
275 = 10 + 4 + 24 + 5 + 30 + 100 + 23 + 35 + 44
275 = 2 + 17 + 24 + 30 + 100 + 23 + 35 + 44
275 = 10 + 2 + 17 + 24 + 30 + 40 + 50 + 23 + 35 + 44
275 = 2 + 4 + 40 + 50 + 100 + 35 + 44
275 = 10 + 2 + 4 + 30 + 50 + 100 + 35 + 44
275 = 17 + 24 + 5 + 50 + 100 + 35 + 44
275 = 4 + 17 + 5 + 30 + 40 + 100 + 35 + 44
275 = 2 + 24 + 30 + 40 + 100 + 35 + 44
275 = 10 + 17 + 24 + 5 + 40 + 100 + 35 + 44
275 = 2 + 4 + 17 + 5 + 30 + 50 + 100 + 23 + 44
275 = 4 + 24 + 30 + 50 + 100 + 23 + 44
275 = 10 + 2 + 17 + 24 + 5 + 50 + 100 + 23 + 44
275 = 10 + 2 + 4 + 17 + 5 + 30 + 40 + 100 + 23 + 44
275 = 10 + 4 + 24 + 30 + 40 + 100 + 23 + 44
275 = 2 + 4 + 5 + 30 + 40 + 50 + 100 + 44
275 = 10 + 2 + 24 + 5 + 40 + 50 + 100 + 44
275 = 17 + 24 + 40 + 50 + 100 + 44
275 = 10 + 17 + 24 + 30 + 50 + 100 + 44
275 = 2 + 4 + 35 + 201 + 33
275 = 2 + 4 + 5 + 30 + 201 + 33
275 = 10 + 2 + 24 + 5 + 201 + 33
275 = 17 + 24 + 201 + 33
275 = 2 + 5 + 35 + 200 + 33
275 = 10 + 4 + 5 + 23 + 200 + 33
275 = 2 + 17 + 23 + 200 + 33
275 = 2 + 40 + 200 + 33
275 = 10 + 2 + 30 + 200 + 33
275 = 4 + 30 + 50 + 100 + 23 + 35 + 33
275 = 10 + 2 + 17 + 5 + 50 + 100 + 23 + 35 + 33
275 = 10 + 24 + 50 + 100 + 23 + 35 + 33
275 = 10 + 4 + 30 + 40 + 100 + 23 + 35 + 33
275 = 10 + 2 + 5 + 40 + 50 + 100 + 35 + 33
275 = 17 + 40 + 50 + 100 + 35 + 33
275 = 10 + 17 + 30 + 50 + 100 + 35 + 33
275 = 10 + 2 + 4 + 17 + 24 + 50 + 100 + 35 + 33
275 = 24 + 5 + 40 + 50 + 100 + 23 + 33
275 = 10 + 2 + 17 + 40 + 50 + 100 + 23 + 33
275 = 10 + 24 + 5 + 30 + 50 + 100 + 23 + 33
275 = 17 + 5 + 30 + 40 + 50 + 100 + 33
275 = 2 + 4 + 17 + 24 + 5 + 40 + 50 + 100 + 33
275 = 10 + 2 + 4 + 17 + 24 + 5 + 30 + 50 + 100 + 33
275 = 2 + 5 + 23 + 245
275 = 30 + 245
275 = 2 + 4 + 24 + 245
275 = 10 + 2 + 4 + 23 + 35 + 201
275 = 4 + 5 + 30 + 35 + 201
275 = 10 + 24 + 5 + 35 + 201
275 = 2 + 4 + 5 + 40 + 23 + 201
275 = 10 + 2 + 4 + 5 + 30 + 23 + 201
275 = 4 + 17 + 30 + 23 + 201
275 = 10 + 17 + 24 + 23 + 201
275 = 2 + 17 + 5 + 50 + 201
275 = 24 + 50 + 201
275 = 4 + 30 + 40 + 201
275 = 10 + 2 + 17 + 5 + 40 + 201
275 = 10 + 24 + 40 + 201
275 = 10 + 2 + 5 + 23 + 35 + 200
275 = 17 + 23 + 35 + 200
275 = 40 + 35 + 200
275 = 10 + 30 + 35 + 200
275 = 10 + 2 + 4 + 24 + 35 + 200
275 = 2 + 50 + 23 + 200
275 = 10 + 2 + 40 + 23 + 200
275 = 17 + 5 + 30 + 23 + 200
275 = 2 + 4 + 17 + 24 + 5 + 23 + 200
275 = 5 + 30 + 40 + 200
275 = 2 + 4 + 24 + 5 + 40 + 200
275 = 10 + 2 + 4 + 24 + 5 + 30 + 200
275 = 4 + 17 + 24 + 30 + 200
275 = 10 + 17 + 40 + 50 + 100 + 23 + 35
275 = 2 + 4 + 17 + 24 + 30 + 40 + 100 + 23 + 35
275 = 4 + 17 + 24 + 5 + 40 + 50 + 100 + 35
275 = 10 + 4 + 17 + 24 + 5 + 30 + 50 + 100 + 35
275 = 10 + 17 + 5 + 30 + 40 + 50 + 100 + 23
275 = 10 + 2 + 4 + 17 + 24 + 5 + 40 + 50 + 100 + 23
275 = 10 + 4 + 17 + 24 + 30 + 40 + 50 + 100


.

Relevant Pages

  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)
  • Auto Write Name and Merge across
    ... Dim Sheetname01 As String ... Dim WeekName01 As String ...
    (microsoft.public.excel.misc)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Re: multiplatform (pocketPC & desktopPC) (Daniel !!)
    ... Friend Versione As String ... Public Sub GetMyConnectionPalmare() ... Dim errorMessages As String ... Private Function GetDS_Desktop(ByVal SQL As String) As DataSet ...
    (microsoft.public.dotnet.framework.compactframework)
  • Help answer these 70-310 questions
    ... One argument is the string ... Dim output As New StringBuilder ... EmployeeLocations. ... You create a strongly named serviced component. ...
    (microsoft.public.cert.exam.mcsd)