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

#### XL123

##### New Member
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?
65181,6
76802,9
87494,7
98137,10
10935
111051
12
Sheet1

#### Attachments

• 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
there is solution for a target sum but not ROW number

know more

#### XL123

##### New Member
there is solution for a target sum but not ROW number

know more

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.

#### A Durfani

##### Active Member
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

Thank you for your help Durfani

#### Tupe77

##### Board Regular
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

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
Thank you for your reply. You are correct am looking for all possible combinations - not just two-line values...

#### tboulden

##### New Member

Pairwise_Combinations.xlsm
ABCDEF
1Row #DataTotal to get
2120100
3265
4372All combination of rows #s adding to to 100?
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.

Replies
1
Views
83
Replies
4
Views
75
Replies
1
Views
62
Replies
0
Views
532
Replies
7
Views
79

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.

### Which adblocker are you using?

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

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