Filter a Filtered array based on a range for summing values

[Kotta]

I have a list of tickers and associated values that's an array resulting from sorting another column using the sort formula. I want to see if I can use a formula in excel to filter that array (C4 through D14) to show me the combination of tickers whose values sum up between 500M and 510M. Any ideas?

 

[Cubist]

This is commonly known as a subset sum problem. It's not something Excel formulas are capable of handling. You'd need VBA or some sort of Add-In Solver. Here's an example of the VBA code:

Combination of numbers that sum or match a target value

www.tushar-mehta.com

 

[hagia_sofia]

Here is an attempt although I am not sure if it is the desired output:

=LET(
tags,C4:C14,
amounts,D4:D14,
min,D1,
max,E1,
combinations,TEXTAFTER(DROP(REDUCE("",TOCOL(amounts),LAMBDA(a,b,VSTACK(a,a&" | "&b))),1)," | ",1),
sum,DROP(REDUCE(0,TOCOL(amounts),LAMBDA(a,b,VSTACK(a,a+b))),1),
filter,FILTER(combinations,(sum>=min)*(sum<=max)),
array,--(IFERROR(DROP(REDUCE("",filter,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," | ")))),1),0)),
total,BYROW(array,LAMBDA(a,SUM(a))),
column,TOCOL(HSTACK(array,total)),
tables,FILTER(column,column<>0),
tag,XLOOKUP(tables,amounts,tags,"Sum:"),
HSTACK(tag,tables))

 

[Kotta]

This is commonly known as a subset sum problem. It's not something Excel formulas are capable of handling. You'd need VBA or some sort of Add-In Solver. Here's an example of the VBA code:

Combination of numbers that sum or match a target value

www.tushar-mehta.com

Thank you

 

[Kotta]

Here is an attempt although I am not sure if it is the desired output:

=LET(
tags,C4:C14,
amounts,D4:D14,
min,D1,
max,E1,
combinations,TEXTAFTER(DROP(REDUCE("",TOCOL(amounts),LAMBDA(a,b,VSTACK(a,a&" | "&b))),1)," | ",1),
sum,DROP(REDUCE(0,TOCOL(amounts),LAMBDA(a,b,VSTACK(a,a+b))),1),
filter,FILTER(combinations,(sum>=min)*(sum<=max)),
array,--(IFERROR(DROP(REDUCE("",filter,LAMBDA(a,b,VSTACK(a,TEXTSPLIT(b," | ")))),1),0)),
total,BYROW(array,LAMBDA(a,SUM(a))),
column,TOCOL(HSTACK(array,total)),
tables,FILTER(column,column<>0),
tag,XLOOKUP(tables,amounts,tags,"Sum:"),
HSTACK(tag,tables))

I see what you are doing. Very interesting approach but not quite what I am looking for. I appreciate it though!!

 

[Cubist]

Here's one way to do it but limited to 20 items since the row limit of Excel is 2^20.

Book1
	C	D	E	F	G
1			Range	500,000,000.00	510,000,000.00
2
3
4				Combinations	Sum
5	A	235,124,356		A, C, D, E, F, H, I, J, K	500,750,160.00
6	B	111,046,425		A, B, D, E, F, J	501,483,426.00
7	C	90,518,145		A, B, C, E, I, J	501,483,426.00
8	D	69,989,865		A, C, D, E, F, G, I, J	501,817,007.00
9	E	49,461,586		A, B, D, E, F, J, K	502,483,426.00
10	F	28,933,306		A, B, C, E, I, J, K	502,483,426.00
11	G	12,456,835		A, C, D, E, F, G, I, J, K	502,817,007.00
12	H	10,389,988		A, B, D, E, F, I	502,960,564.00
13	I	8,405,026		A, B, C, E, H, J	503,468,388.00
14	J	6,927,888		A, C, D, E, F, G, H, J	503,801,969.00
15	K	1,000,000		A, B, D, E, G, H, I, J	503,801,969.00
16				A, B, C, F, G, H, I, J	503,801,969.00
17				A, B, D, E, F, I, K	503,960,564.00
18				A, B, C, E, H, J, K	504,468,388.00
19				A, C, D, E, F, G, H, J, K	504,801,969.00
20				A, B, D, E, G, H, I, J, K	504,801,969.00
21				A, B, C, F, G, H, I, J, K	504,801,969.00
22				A, B, D, E, F, H	504,945,526.00
23				A, B, C, E, H, I	504,945,526.00
24				A, C, D, E, F, G, H, I	505,279,107.00
25				A, B, C, E, G, J	505,535,235.00
26				A, B, D, E, F, H, K	505,945,526.00
27				A, B, C, E, H, I, K	505,945,526.00
28				A, C, D, E, F, G, H, I, K	506,279,107.00
29				A, B, C, E, G, J, K	506,535,235.00
30				A, B, C, D	506,678,791.00
31				A, B, D, E, F, G	507,012,373.00
32				A, B, C, E, G, I	507,012,373.00
33				A, B, C, D, K	507,678,791.00
34				A, B, D, E, F, G, K	508,012,373.00
35				A, B, C, E, G, I, K	508,012,373.00
36				A, B, C, E, G, H	508,997,335.00
37				A, B, D, E, F, I, J	509,888,452.00
38				A, B, C, E, G, H, K	509,997,335.00
Sheet4

Cell Formulas
Range	Formula
F5:G38	F5	=LET(c,DROP(MID(REDUCE(0,C5:C15,LAMBDA(a,c,VSTACK(a,a&", "&c))),4,9^9),1), s,DROP(REDUCE(0,D5:D15,LAMBDA(a,c,VSTACK(a,a+c))),1), SORT(FILTER(HSTACK(c,s),(s>=F1)*(s<=G1)),2))
Dynamic array formulas.

 

[hagia_sofia]

I see what you are doing. Very interesting approach but not quite what I am looking for. I appreciate it though!!

Thanks for the feedback. Could you please specify what is the desired output?