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

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
Asked:
pdvsa
  • 4
  • 4
1 Solution
 
BadotzCommented:
So anything from one hundred "ones" to the number "100"?

Out of curiosity, why do you need to do this?
0
 
Nico BontenbalCommented:
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
the answer is
1+2
3
And when the list is
10,20,11,38,17,21
and the total is 31
the answer is:
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)
        colNumbers.Add 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
                    colNew.Add colNumbers.Item(intCount2)
                Else
                    strUsedNew = strUsedNew & "+" & colNumbers.Item(intCount2)
                    intTotalNew = intTotalNew - Val(colNumbers.Item(intCount2))
                End If
            Next
            GetSumsRec strUsedNew, colNew, intTotalNew
        Next
    End If
End Sub

Open in new window

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
 
pdvsaProject financeAuthor Commented:
Badotz:
<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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Nico BontenbalCommented:
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
 
pdvsaProject 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
 
Nico BontenbalCommented:
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

Open in new window

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
 
pdvsaProject 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
 
Nico BontenbalCommented:
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

Open in new window

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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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