Match a combination of numbers in a range with a Specific Number

[Mike Dove]

Is it possible to create a formula to Match a combination of numbers in a range to Equal to a Specific number?

Ex: Specific number = 1,563
Have a range of cells, maybe 100 cells, with an amount in each cell.
Is it possible to find a combination of 1 cell to ? cells that would equal the number 1,563?

Thanks

[martindwilson]

i found this the other day
from ozgrid
here
http://www.ozgrid.com/forum/showthread.php?t=58000
and wanted to test it but havent as yet coz i dont have the solver add in but you might

[wah2007]

Thanks to Mike for asking a really interesting question and equal thanks to martin for keeping track of other site posts, updating self and sharing with others.

solver coupled with scenario management & pivot table can help in many day-2-day practical situations like budgeting and forecasting. i appeal to everyone that someone should try to explain with the help of an example.

[shg]

This code is (slightly) adapted from Tushar Mehta's original:

Sub test()
    ComboSum 211.01, 100, Range("B1:B30")
End Sub

Sub ComboSum(dTgt As Double, MaxSoln As Integer, rInp As Range)
    ' from http://www.tushar-mehta.com/excel/templates/match_values/index.html

    ' Lists the cells in single-column range rInp that total dTgt
    ' MaxSoln is the number of solutions wanted. Specify zero for all.
    ' Solutions are listed in the column to the right of rInp
    
    Const dEps  As Double = 0.00000001
    Const sSep  As String = ", "

    Dim asSoln() As String  ' solutions
    Dim vInp()  As Variant  ' values in rInp
    Dim daBeg   As Date     ' start time

    Dim WF      As WorksheetFunction

    If rInp.Columns.Count <> 1 Then Exit Sub
    Set WF = Application.WorksheetFunction
    daBeg = Now()
    
    ' get the values from the range
    vInp = WF.Transpose(WF.Transpose(WF.Transpose(rInp.Value)))
    ReDim asSoln(0 To 0)

    RecursiveMatch MaxSoln, dTgt, vInp(), LBound(vInp), 0, dEps, asSoln, "", sSep

    ' list the solutions
    asSoln(UBound(asSoln)) = "Done @ " & Format(Now(), "hh:mm:ss")
    rInp.Offset(, 1).Resize(UBound(asSoln) + 1, 1).Value = WF.Transpose(asSoln)
End Sub

Sub RecursiveMatch(ByVal MaxSoln As Integer, _
                   ByVal dTgt As Double, _
                   ByRef vInp() As Variant, _
                   ByVal iCurrInx As Integer, _
                   ByVal dCurrTot As Double, _
                   ByVal dEps As Double, _
                   ByRef asSoln() As String, _
                   ByVal sSoln As String, _
                   ByVal sSep As String)

    Dim i       As Integer

    For i = iCurrInx To UBound(vInp)
        If Abs(dCurrTot + vInp(i) - dTgt) <= dEps Then
            asSoln(UBound(asSoln)) = dCurrTot + vInp(i) _
                                     & sSep & Format(Now(), "hh:mm:ss") _
                                     & sSoln & sSep & CStr(i)
            If UBound(asSoln) Mod 100 = 0 Then Debug.Print UBound(asSoln) + 1 & " solutions ..."
            If MaxSoln <> 0 Then If UBound(asSoln) >= MaxSoln Then Exit Sub
            ReDim Preserve asSoln(UBound(asSoln) + 1)

        ElseIf dCurrTot + vInp(i) < dTgt - dEps And iCurrInx < UBound(vInp) Then
            RecursiveMatch MaxSoln, dTgt, vInp(), i + 1, _
                           dCurrTot + vInp(i), dEps, asSoln(), _
                           sSoln & sSep & CStr(i), sSep
            If MaxSoln <> 0 Then If UBound(asSoln) >= MaxSoln Then Exit Sub

        End If
    Next i
End Sub

[Mike Dove]

Thanks for the Solutions. I will give them a try. This is my first time at asking a question in an Excel forum. You all are a great help. I think I will continue to hang around. thanks again. Michael

[Mike Dove]

Martin's answer using Solver from ozgrid WORKED!!! Thanks
I will try the other solutions later.

Michael