• Status: Solved
• Priority: Medium
• Security: Public
• Views: 195

# Find Possible Combinations from list (Accounts Payable)

Experts, I am wondering if there is code that will find all possible summs from a number.

Example:
INvoice = 100
need to apply to any possible combo of summs:
10
15
25
25
12
13

It is similar to Goal Seek in Excel but the numbers from list do not change as they would in Goal Seek.

I can invision this as being a button on a form then a msgbox appears asking for the number and then returns any possible numbers from the list.  Or maybe something in Excel would work but dont believe Excel 2k7 has this functionality (maybe 2010 does).

Thank you
0
pdvsa
• 4
• 4
1 Solution

Commented:
So anything from one hundred "ones" to the number "100"?

Out of curiosity, why do you need to do this?
0

Commented:
I'm not sure I know what you mean but I interpreted your question as:
"I have a list of values and a given total and want to know which combinations of values from the list make up the total".
So when the list is
1,2,3
and the total is 3
1+2
3
And when the list is
10,20,11,38,17,21
and the total is 31
10+21
20+11
This piece of code does just that:

``````Option Compare Database
Option Explicit
Private strOutcome As String

Function GetSums(strnumbers As String, intTotal As Integer) As String
Dim colNumbers As New Collection
Dim intCount As Integer

strOutcome = ""
'add the numbers to a collection
For intCount = 0 To UBound(Split(strnumbers, ","))
'Debug.Print Split(strnumbers, ",")(intCount)
Next
GetSumsRec "", colNumbers, intTotal
GetSums = Mid(strOutcome, 3)
End Function

Private Sub GetSumsRec(ByVal strUsed As String, ByVal colNumbers As Collection, ByVal intTotal)
Dim colNew As Collection
Dim strUsedNew As String
Dim intTotalNew As Integer
Dim intCount1 As Integer
Dim intCount2 As Integer

'Debug.Print strUsed, intTotal
If intTotal = 0 Then
'we have a winner!
strOutcome = strOutcome & vbCrLf & Mid(strUsed, 2)
ElseIf intTotal < 0 Then
'no use to continue
Else
'continue
For intCount1 = 1 To colNumbers.Count
strUsedNew = strUsed
intTotalNew = intTotal
Set colNew = New Collection
For intCount2 = intCount1 To colNumbers.Count
If intCount1 <> intCount2 Then
Else
strUsedNew = strUsedNew & "+" & colNumbers.Item(intCount2)
intTotalNew = intTotalNew - Val(colNumbers.Item(intCount2))
End If
Next
GetSumsRec strUsedNew, colNew, intTotalNew
Next
End If
End Sub
``````
If you run:
?GetSums("10,20,11,38,17,21",31)
you'll get:
10+21
20+11
If you run
?GetSums("1,2,3,4,5,6,7,8,9,10",10)
you'll get:
1+2+3+4
1+2+7
1+3+6
1+4+5
1+9
2+3+5
2+8
3+7
4+6
10

The code uses a technique called "recursive programming". When you put in 1,2,3 and a total of 3 it removes the items from the list one by one and tries to create the remaining total so:
2,3 with a total of 2 (3-1)
1,3 with a total of 1 (3-2)
1,2 with a total of 0 (3-3)
For each of these outcomes the process is repeated until a solution if found or the total is less then 0. I'm afraid it is quite difficult to understand for a beginning programmer and it is almost impossible to understand how it works by stepping through the code. But I did some testing and it seems to work fine.
It will probably need some work to implement this in your code, but hopefully this is a good start.
0

Project financeAuthor Commented:
<Out of curiosity, why do you need to do this?
It is to apply payments received to the open invoices.

Nicobo: Yes I think this is what I am after.
Would the code be under a button on a form?  I am not a programmer and will need to know how exactly to implement this such as where the amount paid or source number is input.
0

Commented:
You could add my code in a seperate module. On the form you can add a button that loops to all the open invoices and adds the amount to a comma separated string. Then it uses InputBox to ask the total from the user. It then calls the GetSumsRec function to get the possible sums.

What you probably want is a form with the open invoices and a box where the user enters the total. Then a listbox is filled with all the possible combinations and the user can click on one of the possible combinations to see all the invoices for those amounts.
But that's not a technical question but a development assignment.
0

Project financeAuthor Commented:
how do I call the function?
Private Sub ApplyPmt_Click()

Call GetSums

End Sub

==>I get error of "Argument not optional" when I complile
I am not too familiar with Modules.  I did copy and paste it to a module.
0

Commented:
Looks like you did OK on the module. The thing is calling the GetSums function. It needs some information (parameters) to do its's job. Try changing the code to:

``````Private Sub ApplyPmt_Click()
Dim strNumbers As String
Dim intTotal As Integer

strNumbers = "1,2,3,4,5,6,7,8,9"
intTotal = CInt(InputBox("Enter the total"))
MsgBox (GetSums(strNumbers, intTotal))
End Sub
``````
Now your challenge it to fill the strNumbers variable with the invoice totals from the database. But this is where your persistence is going to kick in :-)

You'll probably want the code to work with decimal numbers. Replace the text
Integer
With
Double
everywhere in the module.
Also replace
If intTotal = 0 Then
with
If Round(intTotal, 2) = 0 Then

Now
GetSums("10.1,20.9,0.5,11.3",21.4)
will also work.
0

Project financeAuthor Commented:
What if the string numbers were found in the form?  I wonder how i could make the code find those numbers.  I dont think i could use me![nameoffield] because that seems too easy.

What do you think?
0

Commented:
It's a bit more complicated. Try this (with a button in the header of a continuous form):
``````Private Sub ApplyPmt_Click()
Dim strNumbers As String
Dim intTotal As Double
Dim rs As Object

Set rs = Me.Recordset
If rs.EOF = False Or rs.BOF = False Then
rs.MoveFirst
End If
Do Until rs.EOF
If Not IsNull(rs![nameoffield]) Then
strNumbers = strNumbers & "," & Trim(Str(rs![nameoffield]))
End If
rs.MoveNext
Loop

strNumbers = Mid(strNumbers, 2)
intTotal = CInt(InputBox("Enter the total"))
MsgBox (GetSums(strNumbers, intTotal))
End Sub
``````
Sorry, but this is as far as I'll go trying to answer this question. The question was about finding possible combinations in a list and I think that part is answered by now.
0

Project financeAuthor Commented:
OK sorry.  I was not aware of it being too complicated and I sorta forgot that yes the qeustion was answered.  thanks for the help.
0

## Featured Post

• 4
• 4
Tackle projects and never again get stuck behind a technical roadblock.