Rotating Schedule based on Unit Volume Share

[artikyulashun]

[TABLE="class: outer_border, width: 1000, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD][/TD]
[TD]Week 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Week 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD]MFG A[/TD]
[TD]MFG B[/TD]
[TD]MFG C[/TD]
[TD][/TD]
[TD]MFG A[/TD]
[TD]MFG B[/TD]
[TD]MFG C[/TD]
[TD][/TD]
[TD]MFG A[/TD]
[TD]MFG B[/TD]
[TD]MFG C[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bananas[/TD]
[TD].6[/TD]
[TD].2[/TD]
[TD].2[/TD]
[TD][/TD]
[TD]ON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Apples[/TD]
[TD].1[/TD]
[TD].4[/TD]
[TD].5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]ON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Oranges[/TD]
[TD].3[/TD]
[TD].5[/TD]
[TD].2[/TD]
[TD][/TD]
[TD][/TD]
[TD]ON[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Columns A,B and C represent Percentage of Unit Volume by MFG for the Fruits to the left.

Using these numbers, I would like to generate a rotating 13-week promotional schedule for each item. Maximum number of weeks in a row that an MFG can have any one specific item on sale is two weeks. However, at the end of the of the 13-weeks, I'd like the number of instances that, for example, MFG A has Bananas on sale equal or near their share or .6.

My initial thought was you could use a ratio to create a patterned binary list of 1 and 0 or "on" and "off" for each item, but perhaps it is easier with these percentages? Perhaps this is simply a manual task.

Your feedback is appreciated.

 

[Eric W]

Intriguing problem. Just to be sure I understand, every week every product will be on sale for one of the manufacturers? If so, I reorganized it a bit so each week has just 1 column, and instead of "ON", it shows the name of the manufacturer instead.

Given that, I started with this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
MFG A	MFG B	MFG C	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13
Bananas	MFG A	MFG C	MFG A	MFG C	MFG C	MFG A	MFG A	MFG A	MFG C	MFG A	MFG C	MFG C	MFG A
Apples	MFG A	MFG C	MFG C	MFG B	MFG C	MFG C	MFG C	MFG B	MFG B	MFG A	MFG B	MFG C	MFG C
Oranges	MFG C	MFG B	MFG B	MFG B	MFG A	MFG B	MFG A	MFG B	MFG B	MFG B	MFG C	MFG C	MFG B

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.6[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.1[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.3[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]=INDEX($B$1:$D$1,MATCH(RAND(),SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3}))))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]


Put the formula in F2, then drag it to the rest of the cells in the table.

The formula in F2 will randomly assign a manufacturer to each cell, based on the ratios in columns A:C. So each manufacturer should be listed roughly the right percentage, but it could vary some. The problem with this is that it does not take into account the maximum 2-week limit. That proved to be a tougher nut to crack. But I came up with this:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
MFG A	MFG B	MFG C	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Week 11	Week 12	Week 13
Bananas	MFG A	MFG A	MFG B	MFG A	MFG C	MFG A	MFG B	MFG A	MFG A	MFG B	MFG B	MFG A	MFG A
Apples	MFG B	MFG C	MFG C	MFG B	MFG C	MFG C	MFG B	MFG B	MFG C	MFG B	MFG B	MFG C	MFG C
Oranges	MFG A	MFG B	MFG B	MFG A	MFG C	MFG B	MFG A	MFG C	MFG B	MFG B	MFG A	MFG B	MFG A

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]0.6[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]0.1[/TD]
[TD="align: right"]0.4[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]0.3[/TD]
[TD="align: right"]0.5[/TD]
[TD="align: right"]0.2[/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]F2[/TH]
[TD="align: left"]{=INDEX($B$1:$D$1,LARGE(LOOKUP(ROW(INDIRECT("1:10"))-1,SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3}))*10,{1,2,3}*SIGN(((D2<>E2)+(E2<>$B$1:$D$1)))),RANDBETWEEN(1,SUM(10*($B2:$D2*SIGN(((D2<>E2)+(E2<>$B$1:$D$1))))))))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]


Put the formula in F2, confirm with Control+Shift+Enter, then drag to the rest of the cells in the table. Also note that this works if your percentages are all 1 digit (.6). If you go to 2 digits, find the 10 in the formula (3 places) and replace it with 100.

This should prevent the same manufacturer more than 2 weeks in a row, and still keep the same ratios. If you want a different list, just press F9. This example shows no MFG A for apples, which is possible. Adding more constraints might push this into the realm of VBA though.

Let me know if this helps.

 

[artikyulashun]

Thank you Eric.

This is highly creative and effective. Greatly appreciate the work that went into this.