Forum Discussion

Chris550's avatar
Chris550
Copper Contributor
Jun 12, 2023

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 ...
  • HansVogelaar's avatar
    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.

Resources