Finding all combinations of rows in a column which sum to a given total

XL123

New Member
Joined
Aug 4, 2014
Messages
11
Hi all,
I have a sheet shown below which has in Column B a list of numbers - this list can have up to 500 values in the column (I only listed 10 numbers in my simple example). Column A has the row # associated with each value. The sum / total I want to get is in cell E2. Its 100 but this is an input cell the value can be changed to put in any number. Cells F6 to F9 have the answers. The row length can change (e.g. F6 to F15) to show all the possible row combinations which give the sum total of 100.
I tried using a solver method but it only gives 1 possible answer. Am thinking its possible to use dynamic array formulas to do this in combination with the new Lambda function - since it accomodates recursion. Does anyone have a possible solution? I put image of problem as an attachment too....

Get subset adding up to a total from a list of #s.xlsx
ABCDEFGHI
1Row #DataTotal to get
2120100
3265
4372All combination of rows #s adding to to 100?
5451Answer:
65181,6
76802,9
87494,7
98137,10
10935
111051
12
Sheet1
 

Attachments

  • Excel sum problem.jpg
    Excel sum problem.jpg
    55.5 KB · Views: 0

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

A Durfani

Active Member
Joined
Apr 12, 2019
Messages
256
Office Version
  1. 2013
Platform
  1. Windows
Thanks Durfani for your help. I tested it. The limitation here is that it only comes up with 1 possible solution and as you mentioned does not indicate which rows.
Yes it comes with one possible solution only I think it is only solution to this problem because there are millions of possibilities if numbers are more

I had same question few days ago and got the solution above in post #2you can see here
 

XL123

New Member
Joined
Aug 4, 2014
Messages
11

ADVERTISEMENT

Thank you for your help Durfani
 

Tupe77

Board Regular
Joined
Nov 26, 2020
Messages
97
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Would this suit your purpose?
I think this is not what you want. You want all possible combinations, not just those with two-line values?

VBA Code:
Sub TS_Test_Combinations()

Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

Dim ListRNG As Range
    Set ListRNG = Selection ' Select the cells that contain the values to be compared OR Set they range to here
Dim TrgSGE As Single: TrgSGE = Range("E2").Value
Dim Cell As Range, SecCell As Range
Dim i As Long: i = 0

For Each Cell In ListRNG
    For Each SecCell In ListRNG
        If Cell.Value + SecCell.Value = TrgSGE Then
            If Not dict.Exists(SecCell.Offset(0, -1).Value & "," & Cell.Offset(0, -1).Value) Then
                dict.Add Cell.Offset(0, -1).Value & "," & SecCell.Offset(0, -1).Value, 0
            End If
        End If
    Next
Next

ActiveSheet.Range("F6").Resize(dict.Count, 1).Value2 = Application.Transpose(dict.Keys)
End Sub
 
Last edited:

XL123

New Member
Joined
Aug 4, 2014
Messages
11

ADVERTISEMENT

Would this suit your purpose?
I think this is not what you want. You want all possible combinations, not just those with two-line values?

VBA Code:
Sub TS_Test_Combinations()

Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")

Dim ListRNG As Range
    Set ListRNG = Selection ' Select the cells that contain the values to be compared OR Set they range to here
Dim TrgSGE As Single: TrgSGE = Range("E2").Value
Dim Cell As Range, SecCell As Range
Dim i As Long: i = 0

For Each Cell In ListRNG
    For Each SecCell In ListRNG
        If Cell.Value + SecCell.Value = TrgSGE Then
            If Not dict.Exists(SecCell.Offset(0, -1).Value & "," & Cell.Offset(0, -1).Value) Then
                dict.Add Cell.Offset(0, -1).Value & "," & SecCell.Offset(0, -1).Value, 0
            End If
        End If
    Next
Next

ActiveSheet.Range("F6").Resize(dict.Count, 1).Value2 = Application.Transpose(dict.Keys)
End Sub
 

XL123

New Member
Joined
Aug 4, 2014
Messages
11
Thank you for your reply. You are correct am looking for all possible combinations - not just two-line values...
 

tboulden

New Member
Joined
Jan 14, 2021
Messages
37
Office Version
  1. 365
Platform
  1. Windows
How about this?

Pairwise_Combinations.xlsm
ABCDEF
1Row #DataTotal to get
2120100
3265
4372All combination of rows #s adding to to 100?
5451Answer:
65181,6
76802,9
87494,7
98137,10
10935
111051This works?
1216
1329
1447
15710
Sheet1
Cell Formulas
RangeFormula
A2:A11A2=SEQUENCE(10)
E12:F15E12=LET( rowRng,A2#, dataRng,$B$2:$B$11, rowPairsCol1,INDEX(rowRng,INT((SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowPairsCol2,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowRng)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowPairs,CHOOSE({1,2},rowPairsCol1,rowPairsCol2), pairSeq,SEQUENCE(ROWS(rowPairs)), dataSums,INDEX(dataRng,INDEX(rowPairs,pairSeq,),1)+INDEX(dataRng,INDEX(rowPairs,pairSeq,2)), FILTER(rowPairs,(dataSums=E2)*(rowPairsCol1<=rowPairsCol2),"") )
Dynamic array formulas.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,242
Messages
5,641,048
Members
417,190
Latest member
Sdwd76

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top