Combinations of multiple products & quantities such total amount matchs given number

[bebo021999]

I have a list of products and their prices in the range "A:D". I have a total amount, for example: 5,000,000.
The total amount is exact, with no allowance for even a 1 cent difference. I need to create an invoice with a maximum of 10 products randomly (with its quantity and unit price, picked from the product list)
and Total amount matching the specified number.
This invoice can have 1, 2, 3, or up to 10 products. Please provide a VBA code snippet to list all possible invoices that can be created.
In the file, the range H3:L14 is an example of an invoice that can be generated.
Thanks in advance!

link of sharing file

Invoice.xlsm

drive.google.com

crossed-post here

Combinations of multiple products & quantities such total amount matchs given number

I have a list of products and their prices in the range "A:D". I have a total amount, for example: 5,000,000. The total amount is exact, with no allowance for even a 1 cent difference. I need to create an invoice with a maximum of 10 products randomly (with its quantity and unit price, picked...

www.excelforum.com

 

[KRice]

Please have a look at an earlier thread where a similar solution was sought. This falls in the area of combinatorics, and the objective then was to find some combination of items whose sum is closest to a target value. You may find something that will work or that can be adapted. Also have a look at offerings from @Xlambda, who documented some LAMBDA functions involving combinatorics.

Choose cells that come closest to adding up to a certain total

This is probably the strangest query I've put before you, and I think it's probably a dud. But, I'm hoping that someone can prove me wrong. I am in charge of our daily lunch here at work, and break that lunch down on my credit card according to how many people from our two buildings (A&D) were...

www.mrexcel.com

 

[Xlambda]

Hello, I am here because @KRice mention me and I kindly thank him for that . So, allow me to propose a solution.
Wrote a recursive function that does the job for a certain chosen number of products "np". Define name "up" - the array of unit prices up: DATA!$D$3:$D$830
PL(np) Product List
The concept is very simple. We select random np-1 products and a random number of units (estimated proportionally with averages) that produce a total. For the rest of the products, we look for a match of a certain product whose unit price multiplied by an array of pcs produces the exact 5000000-random total.
The function does all the cosmetics needed; the conceptual part is short. Also is fully dynamic, 5,000,000 constant can be changed (f variable) and np is not limited to 10, also the product list can be bigger or shorter.
Let me know if it's what you need. The number of total possibilities is astronomic. Also, if you need to freeze these functions to stop recalculating, I have a function for that. hope you like it.

=LAMBDA(np,
    LET(
        f, 5000000,
        t, "Product ",
        o, "0000",
        ta, {"No", "Product", "Quantity", "U.P", "Amount"},
        tp, ROWS(up),
        st, SEQUENCE(tp),
        av, AVERAGE(up),
        n, np - 1,
        e, SEQUENCE( n),
        m, INT(f / av / np) + 15,
        c, SEQUENCE(, m),
        x, INDEX(SORTBY(st, RANDARRAY(tp)), e),
        y, RANDARRAY(n, , 1, m, 1),
        u, INDEX(up, x),
        z, u * y,
        s, f - SUM(z),
        r, FILTER(st, ISNA(XMATCH(st, x))),
        q, SEQUENCE(ROWS(r), m),
        a, INDEX(up, r) * c,
        b, XMATCH(a, s),
        i, AND(ISNA(b)),
        IF(
            i,
            PL(np),
            LET(
                j, MAX(ISNUMBER(b) * q),
                w, QUOTIENT(j - 1, m) + 1,
                l, MOD(j - 1, m) + 1,
                p, INDEX(r, w),
                v, INDEX(up, p),
                k, VSTACK(HSTACK(e, t & TEXT(x, o), y, u, z), HSTACK(np, t & TEXT(p, o), l, v, l * v)),
                VSTACK(ta, k, HSTACK("", "TOTAL", "", "", SUM(TAKE(k, , -1))))
            )
        )
    )
)

Invoice.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	TABLE 1							expected result (manually input)
2	No	Product	Unit	Unit price					Randomly	Randomly				=PL(7)
3	1	Product 0001	pcs	32,500				No	Product	Quantity	U.P	Amount		No	Product	Quantity	U.P	Amount
4	2	Product 0002	pcs	33,500				1	Product 0026	20	33,500	670,000		1	Product 0724	33	33000	1089000
5	3	Product 0003	pcs	32,000				2	Product 0042	50	13,300	665,000		2	Product 0524	32	22600	723200
6	4	Product 0004	pcs	33,000				3	Product 0049	15	32,500	487,500		3	Product 0153	29	23300	675700
7	5	Product 0005	pcs	67,000				4	Product 0188	30	22,600	678,000		4	Product 0136	22	40200	884400
8	6	Product 0006	pcs	34,000				5	Product 0322	25	32,100	802,500		5	Product 0657	2	23300	46600
9	7	Product 0007	pcs	55,000				6	Product 0455	20	11,500	230,000		6	Product 0343	5	55000	275000
10	8	Product 0008	pcs	73,500				7	Product 0655	8	55,000	440,000		7	Product 0761	37	35300	1306100
11	9	Product 0009	pcs	23,300				8	Product 0112	15	40,200	603,000			TOTAL			5000000
12	10	Product 0010	pcs	32,100				9	Product 0556	11	33,000	363,000
13	11	Product 0011	pcs	40,300				10	Product 0013	1	61,000	61,000		=PL(8)
14	12	Product 0012	pcs	25,900					TOTAL			5,000,000		No	Product	Quantity	U.P	Amount
15	13	Product 0013	pcs	61,000							test	5,000,000	OK	1	Product 0676	17	33,000	561,000
16	14	Product 0014	pcs	29,100										2	Product 0825	23	44,000	1,012,000
17	15	Product 0015	pcs	33,400				=PL(3)						3	Product 0819	7	37500	262500
18	16	Product 0016	pcs	40,200				No	Product	Quantity	U.P	Amount		4	Product 0649	7	32500	227500
19	17	Product 0017	pcs	35,300				1	Product 0533	62	67000	4154000		5	Product 0608	17	73500	1249500
20	18	Product 0018	pcs	13,300				2	Product 0143	52	11500	598000		6	Product 0173	19	67000	1273000
21	19	Product 0019	pcs	17,300				3	Product 0813	8	31000	248000		7	Product 0023	33	11500	379500
22	20	Product 0020	pcs	22,600					TOTAL			5000000		8	Product 0789	7	5000	35000
23	21	Product 0021	pcs	20,400											TOTAL			5000000
24	22	Product 0022	pcs	29,300				=PL(4)
25	23	Product 0023	pcs	11,500				No	Product	Quantity	U.P	Amount		=PL(9)
26	24	Product 0024	pcs	33,700				1	Product 0636	45	25900	1165500		No	Product	Quantity	U.P	Amount
27	25	Product 0025	pcs	32,500				2	Product 0296	16	73500	1176000		1	Product 0747	14	32000	448000
28	26	Product 0026	pcs	33,500				3	Product 0536	33	73500	2425500		2	Product 0440	1	73500	73500
29	27	Product 0027	pcs	32,000				4	Product 0753	10	23300	233000		3	Product 0078	21	34000	714000
30	28	Product 0028	pcs	33,000					TOTAL			5000000		4	Product 0659	22	40300	886600
31	29	Product 0029	pcs	67,000										5	Product 0646	3	29300	87900
32	30	Product 0030	pcs	34,000				=PL(5)						6	Product 0030	31	34000	1054000
33	31	Product 0031	pcs	55,000				No	Product	Quantity	U.P	Amount		7	Product 0438	13	34000	442000
34	32	Product 0032	pcs	73,500				1	Product 0007	44	55000	2420000		8	Product 0255	12	33400	400800
35	33	Product 0033	pcs	23,300				2	Product 0510	9	34000	306000		9	Product 0757	29	30800	893200
36	34	Product 0034	pcs	32,100				3	Product 0112	15	40200	603000			TOTAL			5000000
37	35	Product 0035	pcs	40,300				4	Product 0748	24	33000	792000
38	36	Product 0036	pcs	25,900				5	Product 0766	30	29300	879000		=PL(10)
39	37	Product 0037	pcs	30,800					TOTAL			5000000		No	Product	Quantity	U.P	Amount
40	38	Product 0038	pcs	29,100										1	Product 0217	17	32500	552500
41	39	Product 0039	pcs	33,400				=PL(6)						2	Product 0682	21	32100	674100
42	40	Product 0040	pcs	40,200				No	Product	Quantity	U.P	Amount		3	Product 0394	18	32100	577800
43	41	Product 0041	pcs	35,300				1	Product 0745	12	32500	390000		4	Product 0770	14	15600	218400
44	42	Product 0042	pcs	13,300				2	Product 0723	28	32000	896000		5	Product 0603	24	32000	768000
45	43	Product 0043	pcs	17,300				3	Product 0347	37	40300	1491100		6	Product 0728	8	73500	588000
46	44	Product 0044	pcs	22,600				4	Product 0688	36	40200	1447200		7	Product 0511	6	55000	330000
47	45	Product 0045	pcs	20,400				5	Product 0612	23	25900	595700		8	Product 0795	12	11500	138000
48	46	Product 0046	pcs	29,300				6	Product 0801	10	18000	180000		9	Product 0629	12	67000	804000
49	47	Product 0047	pcs	11,500					TOTAL			5000000		10	Product 0758	12	29100	349200
50	48	Product 0048	pcs	33,700											TOTAL			5000000
51	49	Product 0049	pcs	32,500
52	50	Product 0050	pcs	33,500
DATA

Cell Formulas
Range	Formula
B3:B52	B3	="Product " & TEXT(A3,"0000")
N2,H41,N38,H32,N25,H24,H17,N13	N2	=FORMULATEXT(N3)
N3:R11	N3	=PL(7)
A4:A52,H5:H13	A4	=A3+1
K4:K13	K4	=VLOOKUP(I4,$B$3:$D$830,3,0)
L4:L13	L4	=J4*K4
N14:R23	N14	=PL(8)
L15	L15	=SUM(L4:L13)
H18:L22	H18	=PL(3)
H25:L30	H25	=PL(4)
N26:R36	N26	=PL(9)
H33:L39	H33	=PL(5)
N39:R50	N39	=PL(10)
H42:L49	H42	=PL(6)
Dynamic array formulas.

Named Ranges
Name	Refers To	Cells
up	=DATA!$D$3:$D$830	H18, H25, H33, H42, N3, N14, N26, N39, K4:K13

Lambda Functions
Name	Formula
PL	=LAMBDA(np,LET(f,5000000,t,"Product ",o,"0000",ta,{"No","Product","Quantity","U.P","Amount"},tp,ROWS(up),st,SEQUENCE(tp),av,AVERAGE(up),n,np-1,m,INT(f/av/np)+15,c,SEQUENCE(,m),x,INDEX(SORTBY(st,RANDARRAY(tp)),SEQUENCE(n)),y,RANDARRAY(n,,1,m,1),u,INDEX(up,x),z,u*y,s,f-SUM(z),r,FILTER(st,ISNA(XMATCH(st,x))),q,SEQUENCE(ROWS(r),m),a,INDEX(up,r)*c,b,XMATCH(a,s),i,AND(ISNA(b)),IF(i,PL(np),LET(j,MAX(ISNUMBER(b)*q),w,QUOTIENT(j-1,m)+1,l,MOD(j-1,m)+1,p,INDEX(r,w),v,INDEX(up,p),k,VSTACK(HSTACK(SEQUENCE(n),t&TEXT(x,o),y,u,z),HSTACK(np,t&TEXT(p,o),l,v,l*v)),VSTACK(ta,k,HSTACK("","TOTAL","","",SUM(TAKE(k,,-1))))))))