Combine all products with different varieties

[motilulla]

Hello,</SPAN></SPAN>

Combine all products with different varieties to achieve a special offer price package </SPAN></SPAN>

I am looking for a solution on how many special offer price packages can be prepared from the product and variety list of D5:F12, Note: there is Product A To H & Variety X To Z in the offer packages there must be all Products present from A To H one of each, but only one variety price can be chosen from X to Z </SPAN></SPAN>

Here are a few examples...</SPAN></SPAN>
Column H if we make a lowest price offer package than we can pick the lowest price of one variety product and then combine it with one of each product A to H which can be the 135 $</SPAN></SPAN>

Column J if we make a highest price offer package than we can pick the highest price of one variety product and than combine picking one of each product A to H which can be the 476 $</SPAN></SPAN>

Made some offer price packages in Columns L, N, P, R, T and Y, question is how many special offer price packages can be prepared? Or in the range say 135$ to 199, 200$ to 299$, 300$ to 399$ or 400$ to 476$ may be?</SPAN></SPAN>

Below is shown example data...</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
1
2								Min Offer Package		Max Offer Package		Offer Package		Offer Package		Offer Package		Offer Package		Offer Package		Offer Package
3				Variety X	Variety Y	Variety Z		Price $		Price $		Price $		Price $		Price $		Price $		Price $		Price $
4				Price $	Price $	Price $		135		476		157		212		243		311		334		392
5			Product A	9	13	78		9		78		13		9		78		78		78		78
6			Product B	23	34	43		23		43		23		23		43		23		23		23
7			Product C	5	84	11		5		84		5		11		11		11		11		84
8			Product D	59	26	15		15		59		26		26		15		59		26		59
9			Product E	12	20	68		12		68		12		12		12		68		68		20
10			Product F	27	33	40		27		40		27		27		40		27		27		27
11			Product G	33	32	35		32		35		32		35		32		33		32		32
12			Product H	12	69	19		12		69		19		69		12		12		69		69
Sheet2

Thanks In Advance </SPAN></SPAN>
Using version 2000</SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti</SPAN></SPAN>

 

[motilulla]

Hello,</SPAN></SPAN>

Here is my new layout for my previous question.</SPAN></SPAN>

Will it be possible to make all the package prices as shown below:</SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3				Variety X	Variety Y	Variety Z											Package Price
4				Price $	Price $	Price $			Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H	$
5			Product A	9	13	78		Varaities	9	23	5	15	12	27	32	12	135
6			Product B	23	34	43											?
7			Product C	5	84	11											?
8			Product D	59	26	15		Varaities	13	23	5	26	12	27	32	19	157
9			Product E	12	20	68											?
10			Product F	27	33	40											?
11			Product G	33	32	35		Varaities	9	23	11	26	12	27	35	69	212
12			Product H	12	69	19											?
13																	?
14								Varaities	78	43	11	15	12	40	32	12	243
15																	?
16																	?
17								Varaities	78	23	11	59	68	27	33	12	311
18																	?
19																	?
20																	?
21																	?
22								Varaities	78	23	11	26	68	27	32	69	334
23																	?
24																	?
25																	?
26																	?
27																	?
28																	?
29																	?
30								Varaities	78	23	84	59	20	27	32	69	392
31																	?
32																	?
33																	?
34																	?
35								Varaities	78	43	84	59	68	40	35	69	476
36
Sheet3

Regards,
Moti

 

[motilulla]

Bump

 

[motilulla]

Hello,</SPAN></SPAN>

Here is my another attempt instead of finding all min to max sum does this will be possible if I fill the sum in column Q with 270$ and 271$ so the code can fill columns I:P columns from the list D5:F12 to achieve the sum for each row in the example below I have made some sums manually it has took 15 hours + time not sure is correct or there could be more with 270 & 271 </SPAN></SPAN>

Please need help...example what I done manually </SPAN></SPAN>

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3				Variety X	Variety Y	Variety Z											Package Price
4				Price $	Price $	Price $			Product A	Product B	Product C	Product D	Product E	Product F	Product G	Product H	$
5			Product A	9	13	78		Varaities	78	43	11	26	20	40	33	19	270
6			Product B	23	34	43		Varaities	78	34	5	59	20	27	35	12	270
7			Product C	5	84	11		Varaities	78	23	11	59	20	27	33	19	270
8			Product D	59	26	15		Varaities	78	23	11	59	12	40	35	12	270
9			Product E	12	20	68		Varaities	78	23	11	15	12	27	35	69	270
10			Product F	27	33	40		Varaities	78	23	5	59	20	40	33	12	270
11			Product G	33	32	35		Varaities	78	23	5	59	20	33	33	19	270
12			Product H	12	69	19		Varaities	78	23	5	15	68	27	35	19	270
13								Varaities	78	23	5	15	20	27	33	69	270
14								Varaities	78	23	5	15	12	33	35	69	270
15								Varaities	13	43	84	26	20	40	32	12	270
16								Varaities	13	43	84	26	20	33	32	19	270
17								Varaities	13	34	11	59	68	40	33	12	270
18								Varaities	13	34	11	59	68	33	33	19	270
19								Varaities	13	34	11	59	12	40	32	69	270
20								Varaities	13	34	11	15	68	27	33	69	270
21								Varaities	13	23	11	59	20	40	35	69	270
22								Varaities	13	23	11	26	68	27	33	69	270
23								Varaities	13	23	5	26	68	33	33	69	270
24								Varaities	13	23	84	59	20	27	32	12	270
25								Varaities	13	23	84	59	12	27	33	19	270
26								Varaities	9	43	11	59	68	33	35	12	270
27								Varaities	9	43	11	59	20	27	32	69	270
28								Varaities	9	43	5	59	20	33	32	69	270
29								Varaities	9	34	11	59	20	33	35	69	270
30								Varaities	9	34	5	26	68	27	32	69	270
31								Varaities	9	34	84	59	12	27	33	12	270
32								Varaities	9	23	11	15	68	40	35	69	270
33								Varaities	9	23	84	15	68	27	32	12	270
34								Varaities	78	43	5	59	12	27	35	12	271
35								Varaities	78	23	5	59	12	40	35	19	271
36								Varaities	78	23	5	26	68	27	32	12	271
37								Varaities	13	43	11	59	68	33	32	12	271
38								Varaities	13	43	5	59	20	27	35	69	271
39								Varaities	13	43	84	26	20	40	33	12	271
40								Varaities	13	43	84	26	20	33	33	19	271
41								Varaities	13	34	5	59	68	40	33	19	271
42								Varaities	13	34	84	26	20	40	35	19	271
43								Varaities	13	23	11	15	68	40	32	69	271
44								Varaities	13	23	84	59	20	27	33	12	271
45								Varaities	13	23	84	59	12	33	35	12	271
46								Varaities	9	43	11	59	68	27	35	19	271
47								Varaities	9	43	11	59	20	27	33	69	271
48								Varaities	9	43	11	59	12	33	35	69	271
49								Varaities	9	43	5	59	68	33	35	19	271
50								Varaities	9	43	5	59	20	33	33	69	271
51								Varaities	9	43	5	15	68	27	35	69	271
52								Varaities	9	34	11	15	68	33	32	69	271
53								Varaities	9	34	5	59	20	40	35	69	271
54								Varaities	9	23	11	26	68	33	32	69	271
55								Varaities	9	23	84	59	12	40	32	12	271
56								Varaities	9	23	84	59	12	33	32	19	271
57								Varaities	9	23	84	15	68	27	33	12	271
58								Varaities	9	23	84	15	12	27	32	69	271
Sheet4

Thanks In Advance </SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

Bump

 

[motilulla]

Hello,</SPAN></SPAN>

May be my layout does not fit to get any VBA solutions please anyone can help may be creating another way to get answer of my query, if not a VBA than any formula solution </SPAN></SPAN>

Thanks In Advance </SPAN></SPAN>

Regards,</SPAN></SPAN>
Moti </SPAN></SPAN>

 

[motilulla]

Bump

 

[motilulla]

Bump

 

[motilulla]

bump

bump

 

[motilulla]

bump

Really I have broken my head , trying to change question make question easier but can't may could be there a way,

In a simple way I want to generate a all possible sum of given data from D5:F12 within 8 positions. or may can be generated fixing target sum?

Regards,
Moti