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

[XL123]

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
	A	B	C	D	E	F	G	H	I
1	Row #	Data			Total to get
2	1	20			100
3	2	65
4	3	72			All combination of rows #s adding to to 100?
5	4	51			Answer:
6	5	18				1,6
7	6	80				2,9
8	7	49				4,7
9	8	13				7,10
10	9	35
11	10	51
12
Sheet1

 

[Sufiyan97]

Visit below link
there is solution for a target sum but not ROW number

Accounts Receivable Problem | Sulprobil

berndplumhoff.gitbook.io

know more

Accounts Receivable Challenge

www.mrexcel.com

 

[XL123]

Visit below link
there is solution for a target sum but not ROW number

Accounts Receivable Problem | Sulprobil

berndplumhoff.gitbook.io

know more

Accounts Receivable Challenge

www.mrexcel.com

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.

 

[Sufiyan97]

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

Find Expected SUM

Is it possible to find expected SUM from one sheet to another? Below Sheet Blalock2 amounts in column B have may have SUM from Blalock1 For Example Amount in cell B3 is 5,498.35 so I want to find smaller amounts from Blalock1 which totals 5,498.35 AB1DATE 2SPENT...

www.mrexcel.com

 

[XL123]

Thank you for your help Durfani

 

[Tupe77]

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?

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]

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?

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]

Thank you for your reply. You are correct am looking for all possible combinations - not just two-line values...

 

[Sufiyan97]

Thank you for your help Durfani

Your're welcome
Glad to help

 

[tboulden]

How about this?

Pairwise_Combinations.xlsm
	A	B	C	D	E	F
1	Row #	Data			Total to get
2	1	20			100
3	2	65
4	3	72			All combination of rows #s adding to to 100?
5	4	51			Answer:
6	5	18				1,6
7	6	80				2,9
8	7	49				4,7
9	8	13				7,10
10	9	35
11	10	51			This works?
12					1	6
13					2	9
14					4	7
15					7	10
Sheet1

Cell Formulas
Range	Formula
A2:A11	A2	=SEQUENCE(10)
E12:F15	E12	=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.