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 |
---|
1 | | 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 |
2 | Bananas | 0.6 | 0.2 | 0.2 | | 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 |
3 | Apples | 0.1 | 0.4 | 0.5 | | 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 |
4 | Oranges | 0.3 | 0.5 | 0.2 | | 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>
</tbody>
Sheet1
Worksheet Formulas
Cell | Formula |
---|
F2 | =INDEX($B$1:$D$1,MATCH(RAND(),SUBTOTAL(9,OFFSET($A2,0,0,1,{1,2,3})))) |
---|
<tbody>
</tbody> |
<tbody>
</tbody>
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 |
---|
1 | | 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 |
2 | Bananas | 0.6 | 0.2 | 0.2 | | 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 |
3 | Apples | 0.1 | 0.4 | 0.5 | | 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 |
4 | Oranges | 0.3 | 0.5 | 0.2 | | 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>
</tbody>
Sheet2
Array Formulas
Cell | Formula |
---|
F2 | {=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))))))))} |
---|
<tbody>
</tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself |
<tbody>
</tbody>
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.