VBA for Optimal Combinations with Multiple Constraints

[Manny82]

Hi All,

I am new to excel VBA - but I do understand after I read the code. I have the attached spreadsheet where I have sheet "Combinations" with all possible combinations for P1-P5 items. While the best combination is ranked based on Value, I want to create an output for 52 weeks in a year using the constrains from "Constraints" sheet.

So for example P1 with a P_Val of 22 - in 52 weeks you can only have a total of between 29-31 weeks - this is while also optimising for the highest possible value. This is easy if we are looking at just one P - but in my case I have 5 Ps and its becoming hard. I have manually done a sample output in "Output' sheet - while it manages most of the constraints it doesn't work exactly. Any help to do this in VBA would be total awesome. Thanks heaps for help.

Combinations sheet:

combinations.xlsx
	G
2	1
Combinations

Constraints Sheet

combinations.xlsx
	B
2	22
Constraints

Output Sheet

combinations.xlsx
	J
22
Output

 

[Manny82]

Sorry i didn't copy the image properly

Combinations Sheet:

combinations.xlsx
	A	B	C	D	E	F	G
1	P1	P2	P3	P4	P5	Value	Rank
2	10	13.5	30	12	8	393690.1	1
3	10	13.5	30	10.5	8	337355.3	2
4	10	13.5	25	12	8	334789	3
5	10	13.5	30	12	7	324510.6	4
6	22	13.5	14	12	8	283257.2	8
7	10	11.5	30	12	8	297441.2	5
8	10	13.5	20.5	12	8	288501.5	6
9	10	13.5	25	10.5	8	287976.6	7
10	10	13.5	30	10.5	7	278784.7	9
11	10	13.5	25	12	7	276636	10
12	10	13.5	14	12	8	268755.7	11
13	22	13.5	14	10.5	8	259611.8	14
14	10	13.5	30	8.5	8	265838.3	12
15	10	13.5	30	12	6	260994.6	13
16	22	11.5	14	12	8	244223.7	19
17	22	13.5	14	12	7	243927.1	20
18	10	11.5	30	10.5	8	256361.2	15
19	19	13.5	14	12	8	243227.4	21
20	10	11.5	25	12	8	254166.2	16
21	10	11.5	30	12	7	245930.3	18
22	10	13.5	20.5	10.5	8	249461.5	17
23	22	13.5	14	5	8	221308.2	33
24	10	13.5	14	10.5	8	235486.9	24
25	22	13.5	14	8.5	8	226698.6	26
26	22	11.5	14	10.5	8	224377.4	29
27	10	13.5	20.5	12	7	239229.7	22
28	10	13.5	25	10.5	7	238639.3	23
29	22	13.5	14	10.5	7	224069.8	30
30	19	13.5	14	10.5	8	222993.2	32
31	10	13.5	14	12	7	225060.1	27
32	22	9.5	14	12	8	209664.5	43
33	10	13.5	25	8.5	8	228443.1	25
34	10	13.5	30	10.5	6	224956.7	28
35	22	11.5	14	12	7	210491.6	41
36	10	9.5	30	12	8	218749.5	37
37	10	13.5	25	12	6	223311.1	31
38	19	11.5	14	12	8	209200.7	45
39	10	11.5	20.5	12	8	220770	34
Combinations

Constraints Sheet:

combinations.xlsx
	A	B	C	D
1	Item	P_Val	Frequency_Min	Frequency_Max
2	P1	22	29	31
3	P1	19	4	6
4	P1	10	11	15
5	P1	16	4	6
6	P3	30	29	31
7	P3	20	4	6
8	P3	14	11	15
9	P3	25	4	6
10	P4	12	29	31
11	P4	10.5	4	6
12	P4	5	11	15
13	P4	8.5	4	6
14	P2	13.5	29	31
15	P2	11.5	4	6
16	P2	7.5	11	15
17	P2	9.5	4	6
18	P5	8	29	31
19	P5	7	4	6
20	P5	5	11	15
21	P5	6	4	6
Constraints

Output Sheet:

combinations.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1	WEEK	P1	P2	P3	P4	P5	Value	HALF			P1	Count	P2	Count	P3	Count	P4	Count	P5	Count
2	1	10	13.5	30	12	5	201749.4646	1H		1H	22	16	13.5	15	30	14	12	15	8	14
3	2	22	7.5	14	12	8	169083.0839	1H		1H	19	1	11.5	2	25	2	10.5	2	7	2
4	3	10	13.5	30	12	5	201749.4646	1H		1H	16	2	9.5	2	20.5	2	8.5	2	6	3
5	4	22	7.5	14	12	8	169083.0839	1H		1H	10	7	7.5	7	14	8	5	7	5	7
6	5	22	11.5	30	8.5	8	77018.12466	1H				26		26		26		26		26
7	6	22	7.5	14	12	8	169083.0839	1H
8	7	10	13.5	30	12	5	201749.4646	1H		2H	22	14	13.5	16	30	16	12	15	8	17
9	8	22	7.5	14	12	8	169083.0839	1H		2H	19	3	11.5	2	25	1	10.5	2	7	2
10	9	10	13.5	30	12	5	201749.4646	1H		2H	16	2	9.5	1	20.5	2	8.5	2	6	0
11	10	22	7.5	14	12	8	169083.0839	1H		2H	10	7	7.5	7	14	7	5	7	5	7
12	11	10	13.5	30	12	5	201749.4646	1H				26		26		26		26		26
13	12	22	7.5	30	5	8	96142.77864	1H
14	13	19	13.5	14	12	8	243227.4457	1H
15	14	22	9.5	25	10.5	8	80309.37815	1H
16	15	22	13.5	14	5	7	196220.1893	1H
17	16	10	13.5	30	12	5	201749.4646	1H
18	17	22	13.5	14	5	7	196220.1893	1H
19	18	16	13.5	20.5	12	8	108877.9045	1H
20	19	22	13.5	30	5	6	112269.1666	1H
21	20	16	13.5	20.5	12	8	108877.9045	1H
22	21	22	11.5	30	8.5	8	77018.12466	1H
23	22	22	13.5	30	5	6	112269.1666	1H
24	23	22	7.5	30	5	8	96142.77864	1H
25	24	10	13.5	30	12	5	201749.4646	1H
26	25	22	13.5	30	5	6	112269.1666	1H
27	26	22	9.5	25	10.5	8	80309.37815	1H
28	27	10	13.5	30	12	5	201749.4646	2H
29	28	22	11.5	30	8.5	8	77018.12466	2H
30	29	10	13.5	30	12	5	201749.4646	2H
31	30	22	7.5	30	12	8	71630.99269	2H
32	31	22	13.5	14	5	7	196220.1893	2H
33	32	10	13.5	30	12	5	201749.4646	2H
34	33	22	7.5	30	12	8	71630.99269	2H
35	34	22	13.5	14	5	7	196220.1893	2H
36	35	10	13.5	30	12	5	201749.4646	2H
37	36	22	7.5	30	12	8	71630.99269	2H
38	37	22	13.5	14	5	8	221308.1703	2H
39	38	10	13.5	30	12	5	201749.4646	2H
40	39	22	7.5	30	5	8	96142.77864	2H
41	40	19	13.5	14	12	8	243227.4457	2H
42	41	22	7.5	30	5	8	96142.77864	2H
43	42	19	13.5	14	12	8	243227.4457	2H
44	43	22	7.5	30	5	8	96142.77864	2H
45	44	16	13.5	20.5	12	8	108877.9045	2H
46	45	22	13.5	14	10.5	8	259611.8027	2H
47	46	10	13.5	30	12	5	201749.4646	2H
48	47	22	7.5	30	5	8	96142.77864	2H
49	48	16	13.5	20.5	12	8	108877.9045	2H
50	49	19	13.5	14	12	8	243227.4457	2H
51	50	22	11.5	30	8.5	8	77018.12466	2H
52	51	22	9.5	25	10.5	8	80309.37815	2H
53	52	10	13.5	30	12	5	201749.4646	2H
Output

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
S1	Expression	=(#REF!="Base")	text	NO
S1	Expression	=(#REF!="Deep")	text	NO
S1	Expression	=(#REF!="Shallow")	text	NO
S1	Expression	=(#REF!="HP")	text	NO
Q1	Expression	=(#REF!="Base")	text	NO
Q1	Expression	=(#REF!="Deep")	text	NO
Q1	Expression	=(#REF!="Shallow")	text	NO
Q1	Expression	=(#REF!="HP")	text	NO
O1	Expression	=(#REF!="Base")	text	NO
O1	Expression	=(#REF!="Deep")	text	NO
O1	Expression	=(#REF!="Shallow")	text	NO
O1	Expression	=(#REF!="HP")	text	NO
M1	Expression	=(#REF!="Base")	text	NO
M1	Expression	=(#REF!="Deep")	text	NO
M1	Expression	=(#REF!="Shallow")	text	NO
M1	Expression	=(#REF!="HP")	text	NO
K1	Expression	=(#REF!="Base")	text	NO
K1	Expression	=(#REF!="Deep")	text	NO
K1	Expression	=(#REF!="Shallow")	text	NO
K1	Expression	=(#REF!="HP")	text	NO
I1	Expression	=(R1="Base")	text	NO
I1	Expression	=(R1="Deep")	text	NO
I1	Expression	=(R1="Shallow")	text	NO
I1	Expression	=(R1="HP")	text	NO
I1	Expression	=(AJ1="Base")	text	NO
I1	Expression	=(AJ1="Deep")	text	NO
I1	Expression	=(AJ1="Shallow")	text	NO
I1	Expression	=(AJ1="HP")	text	NO
A1	Expression	=(#REF!="Base")	text	NO
A1	Expression	=(#REF!="Deep")	text	NO
A1	Expression	=(#REF!="Shallow")	text	NO
A1	Expression	=(#REF!="HP")	text	NO
T1	Expression	=(AB1="Base")	text	NO
T1	Expression	=(AB1="Deep")	text	NO
T1	Expression	=(AB1="Shallow")	text	NO
T1	Expression	=(AB1="HP")	text	NO
R1	Expression	=(Z1="Base")	text	NO
R1	Expression	=(Z1="Deep")	text	NO
R1	Expression	=(Z1="Shallow")	text	NO
R1	Expression	=(Z1="HP")	text	NO
P1	Expression	=(X1="Base")	text	NO
P1	Expression	=(X1="Deep")	text	NO
P1	Expression	=(X1="Shallow")	text	NO
P1	Expression	=(X1="HP")	text	NO
N1	Expression	=(V1="Base")	text	NO
N1	Expression	=(V1="Deep")	text	NO
N1	Expression	=(V1="Shallow")	text	NO
N1	Expression	=(V1="HP")	text	NO
L1	Expression	=(T1="Base")	text	NO
L1	Expression	=(T1="Deep")	text	NO
L1	Expression	=(T1="Shallow")	text	NO
L1	Expression	=(T1="HP")	text	NO
B1:F53	Expression	=(#REF!="Base")	text	NO
B1:F53	Expression	=(#REF!="Deep")	text	NO
B1:F53	Expression	=(#REF!="Shallow")	text	NO
B1:F53	Expression	=(#REF!="HP")	text	NO