Forum Discussion

Chris550's avatar
Chris550
Copper Contributor
Jun 12, 2023
Solved

Find Best Combination of Values in Different Columns Closest to Target Value

Hello,   I need to find the best combination of values from multiple columns that are closest (not over) to a given target value. Only 1 value from each column can be used. What would be the best ...
excel
Formulas and Functions
  • HansVogelaar's avatar
    HansVogelaar
    Jun 12, 2023

    Chris550 

    One option is a recursive VBA function. It works very well with your example, but it would bog down if the range to process is large.

    Code to be copied into a module in the Visual Basic Editor:

    Dim v() As Variant
Dim s() As Double
Dim m() As Long
Dim mr() As Long
Dim n1, n2 As Long
Dim t As Double
Dim d As Double
Dim c As Long

Function FindSolution(rng As Range, tgt As Double)
    v = rng.Value
    n1 = UBound(v, 1)
    n2 = UBound(v, 2)
    ReDim r(1 To n2)
    ReDim w(1 To n2)
    ReDim s(0 To n2)
    ReDim m(1 To n2)
    ReDim mr(1 To n2)
    t = tgt
    d = 10000000
    Call ProcessColumn(1)
    For c = 1 To n2
        w(c) = v(m(c), c)
    Next c
    FindSolution = w
End Function

Sub ProcessColumn(c As Long)
    Dim r As Long
    Dim i As Long
    For r = 1 To n1
        mr(c) = r
        s(c) = s(c - 1) + v(r, c)
        If s(c) <= t Then
            If c = n2 Then
                If t - s(c) < d Then
                    d = t - s(c)
                    For i = 1 To n2
                        m(i) = mr(i)
                    Next i
                End If
            Else
                Call ProcessColumn(c + 1)
            End If
        End If
    Next r
End Sub

     Use like this in a cell formula:

     

    =FindSolution(A2:D11,G2)

     

    The workbook must be saved as a macro-enabled workbook (*.xlsm).

    See the attached sample workbook.

Chris550's avatar
Chris550
Copper Contributor
Jun 14, 2023

HansVogelaar 

Thanks for the response.

This seems like a great solution.

Would it be possible to have excel output all best combinations?

For example, the solution in your worksheet is:

1, 5, .9, 18

However the following would also be candidates for best combination:

(2, 4, .9, 18), (3, 3, .9, 18), (5, 8, .9, 11) plus many more combinations.

Patrick2788's avatar
Patrick2788
Silver Contributor
Jun 14, 2023

Chris550 

This example finds all possible combinations of numbers 1 to 9 that sum to exactly 25.  The more unique numbers you have, the more difficult (and eventually, impossible) this becomes with a formula.

 

 

 

=LET(
    combinations, SUM(COMBIN(9, SEQUENCE(9))),
    BinMatrix, DEC2BIN(SEQUENCE(combinations), 9),
    NumMatrix, MAKEARRAY(
        combinations,
        9,
        LAMBDA(r, c, c * MID(INDEX(BinMatrix, r), c, 1))
    ),
    crit, BYROW(NumMatrix, LAMBDA(row, SUM(row))),
    FILTER(NumMatrix, crit = 25)
)

 

The attached workbook steps through it by each piece.

 

 

 

Resources