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

XL123

New Member
Joined
Aug 4, 2014
Messages
14
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: 38
Impressive tboulden! This is a great use of dynamic array formulas. How will the formula handle if 3 or 4 combinations of numbers having to be combined to get a required total though?
 
Upvote 0

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
The essence of this is coming up with pairwise-combinations of 2 lists; in your original its pairwise-combinations of the list of Rows with itself. For 3 or 4, you take the combinations from before and combine again with the list of Rows. Repeat as many times as you like; I've done 3 below, perhaps I'll try to set up a LAMBDA for the more general case later.

Pairwise_Combinations.xlsm
ABCDEFGHIJKLMNOPQRS
1Row #DataTotal to getTotal to getTotal to getTotal to get
2120100172<- Add Row 3118<- Add Row 5113<- Add Row 8
3265
4372All combination of rows #s adding to to 100?
5451Answer:
65181,6
76802,9
87494,7
98137,10If you don't want rows double-counted, change <= to < in FILTER
10935
111051Pair SumTriple SumTriple SumTriple Sum
1216136156168
1329239177289
1447347259478
1571037104577810
165710
17
TripleSum
Cell Formulas
RangeFormula
A2:A11A2=SEQUENCE(10)
I2I2=E2+B4
M2M2=E2+B6
Q2Q2=E2+B9
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),"") )
I12:K15I12=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), rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3), tripSeq,SEQUENCE(ROWS(rowTrips)), dataSums,INDEX(dataRng,INDEX(rowTrips,tripSeq,),1)+INDEX(dataRng,INDEX(rowTrips,tripSeq,2))+INDEX(dataRng,INDEX(rowTrips,tripSeq,3)), FILTER(rowTrips,(dataSums=I2)*(rowTripsCol1<=rowTripsCol2)*(rowTripsCol2<=rowTripsCol3),"") )
M12:O16M12=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), rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3), tripSeq,SEQUENCE(ROWS(rowTrips)), dataSums,INDEX(dataRng,INDEX(rowTrips,tripSeq,),1)+INDEX(dataRng,INDEX(rowTrips,tripSeq,2))+INDEX(dataRng,INDEX(rowTrips,tripSeq,3)), FILTER(rowTrips,(dataSums=M2)*(rowTripsCol1<=rowTripsCol2)*(rowTripsCol2<=rowTripsCol3),"") )
Q12:S15Q12=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), rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1), rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1), rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3), tripSeq,SEQUENCE(ROWS(rowTrips)), dataSums,INDEX(dataRng,INDEX(rowTrips,tripSeq,),1)+INDEX(dataRng,INDEX(rowTrips,tripSeq,2))+INDEX(dataRng,INDEX(rowTrips,tripSeq,3)), FILTER(rowTrips,(dataSums=Q2)*(rowTripsCol1<=rowTripsCol2)*(rowTripsCol2<=rowTripsCol3),"") )
Dynamic array formulas.
 
Upvote 0
Thanks, glad to help! Struggled with a similar problem a month ago and came up with this approach. One caution: you want the longer list to be first when doing the combinations; if lists are same length, order doesn't matter.
 
Upvote 0
Thanks, glad to help! Struggled with a similar problem a month ago and came up with this approach. One caution: you want the longer list to be first when doing the combinations; if lists are same length, order doesn't matter.
Thank you for the tip. I appreciate your advice
 
Upvote 0
I'm trying to do this with 4 columns, but it seems to error out after searching through 16 rows, anyone have any ideas? I'll leave my formula below

= LET( rowRng,A2#, dataRng,$B$2:$B$150,
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),
rowTripsCol1,INDEX(INDEX(rowPairs,,1),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1),
rowTripsCol2,INDEX(INDEX(rowPairs,,2),INT((SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1)/ROWS(rowRng))+1),
rowTripsCol3,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowPairs)*ROWS(rowRng))-1,ROWS(rowRng))+1),
rowTrips,CHOOSE({1,2,3},rowTripsCol1,rowTripsCol2,rowTripsCol3),
row4Col1,INDEX(INDEX(rowTrips,,1),INT((SEQUENCE(ROWS(rowTrips)*ROWS(rowPairs))-1)/ROWS(rowPairs))+1),
row4Col2,INDEX(INDEX(rowTrips,,2),INT((SEQUENCE(ROWS(rowTrips)*ROWS(rowPairs))-1)/ROWS(rowPairs))+1),
row4Col3,INDEX(INDEX(rowTrips,,3),INT((SEQUENCE(ROWS(rowTrips)*ROWS(rowPairs))-1)/ROWS(rowPairs))+1),
row4Col4,INDEX(rowRng,MOD(SEQUENCE(ROWS(rowTrips)*ROWS(rowPairs))-1,ROWS(rowRng))+1),
rowFours,CHOOSE({1,2,3,4},row4Col1,row4Col2,row4Col3,row4Col4),
fourSeq,SEQUENCE(ROWS(rowFours)),
dataSums,INDEX(dataRng,INDEX(rowFours,fourSeq,1),1)+INDEX(dataRng,INDEX(rowFours,fourSeq,2))+INDEX(dataRng,INDEX(rowFours,fourSeq,3))+INDEX(dataRng,INDEX(rowFours,fourSeq,4)),
FILTER(rowFours,(dataSums=F5)*(row4Col1<row4Col2)*(row4Col2<row4Col3)*(row4Col3<row4Col4),””))
 
Upvote 0
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
How can I download the file?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,972
Members
448,537
Latest member
Et_Cetera

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
Back
Top