Find all combinations from multiple columns that add up to an exact value

Question

I have four columns

I want to find all combinations of numbers from each column that add up to an exact value. Is there a VBA script that can help me achieve this goal?

I am looking for an output like:

Let's say that I want the values to add up to 2721, then the code should return the combination as (1,3,6,7) i.e. the corresponding row of each column and so on until it finds all such combinations. If it is not able to find the exact sum then find the nearest sum to that value.

Have you searched for posts about "combinations" on here? You should do, as this has been asked before. — Solar Mike, Jan 25 '19 at 04:32
That is in Python. I am looking for VBA. Could you point me the one you are referring? — Ankur, Jan 25 '19 at 04:37
This is the first result if you type in the word "combinations" in the search box and hit enter, you need to do that as there is more than 1 result that may be relevant... see https://stackoverflow.com/q/32853339/4961700 — Solar Mike, Jan 25 '19 at 04:43
https://stackoverflow.com/questions/19780016/vba-write-all-possible-combinations-of-4-columns-of-data/19780307#19780307 — Tim Williams, Jan 25 '19 at 06:42

score 1 · Answer 1 · answered Jan 25 '19 at 05:35

It can be easily done in VBA using ReSize method, Sum function, Loops and conditional statements. Below is an exampe with sample data as:

Step 1: Write a VBA Macro similar to this.

Option Explicit

Function Check_Combination_Sum(ByVal lngSum As Long) As String

    Dim rng As Range
    Dim rngSource As Range

    Set rngSource = Range("A2:A16")

    For Each rng In rngSource.Cells
        If WorksheetFunction.Sum(rng.Resize(1, 4)) = lngSum Then
            rng.Resize(1, 4).Interior.Color = RGB(200, 255, 220)
            Check_Combination_Sum = rng.Row & "," & Check_Combination_Sum
        End If
    Next

    If Len(Check_Combination_Sum) > 1 Then
        Check_Combination_Sum = Left(Check_Combination_Sum, Len(Check_Combination_Sum) - 1)
    End If
End Function

Sub Check_Data()
    MsgBox Check_Combination_Sum(197)
End Sub

Step 2: The code above will highlight the rows containing the data with total sum as given and also pop the rows number. Modify the code to suit your requirements.

Find all combinations from multiple columns that add up to an exact value

1 Answers1