Creation of production schedule (staggered distribution)

Hello My Friends!

I have a task to create a production schedule for different types of products. And i need to distribute this product types between predifined number of units. To solve it with formulas not so easy...

Please find attached file example with my notes. I try to explain problem in more detail. Also for better imagination I create 2 examples (one easy and one complicated).

Could you plz help me with this issue and write vba code. I am "zero" in coding

Please ask if you have any questions.

Best Regards,
Igor.

ISMI,
Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

---

Could you plz help me with this issue and write vba code:
Go to the Commercial Services section of this site http://www.excelforum.com/commercial-services/
And read the FAQ on how to solicit paid help.

Thank you for fixing the title. I think I found a formula solution. It requires a helper column, though. In C2, enter the value "1". Then in C3:

=IF(B3="","",C2+B3)

You can make the text in the helper cells white if you don't want them to be visible, or you can put them somewhere else in the workbook and hide them. With the helper column in place, use the following formula in D9:

=IF($C9="","",IF(MOD(D$8,6)<>MOD(ROW()-ROW(D$8),6),"",IF(D$8=1,INDEX($A$3:$A$6,1),INDEX($A$2:$A$6,MATCH(IF(MOD(SUMPRODUCT((OFFSET($C$9:$C$14,0,1,,D$8-1)<>"")+0)+1,SUM($B$3:$B$6))=0,SUM($B$3:$B$6),MOD(SUMPRODUCT((OFFSET($C$9:$C$14,0,1,,D$8-1)<>"")+0)+1,SUM($B$3:$B$6))),$C$2:$C$6,1)+1))))

Fill down and right and you should be all set. You can add the colors with some basic conditional formatting based on the letter in the cell.

I've checked a few different variations and it seems to hold up, but take a look at the attachment to see if works as desired.

How about if it detects separate groups by the space in between groups? For this solution, you'll have to sacrifice cell $C$8: leave this cell blank, then use the following formula, array-entered, in D9:

=IF($C9="","",IF(MOD(D$8,MIN(IF($C9:$C$15="",ROW($C9:$C$15)-ROW($C$8)-1))-MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1)))))<>MOD(ROW()-ROW(D$8)+MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1)))),MIN(IF($C9:$C$15="",ROW($C9:$C$15)-ROW($C$8)-1))-MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1))))),"",IF(D$8=1,INDEX($A$3:$A$6,1),INDEX($A$2:$A$6,MATCH(IF(MOD(SUMPRODUCT((OFFSET($C$9,MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1)))),1,MIN(IF($C9:$C$15="",ROW($C9:$C$15)-ROW($C$8)-1))-MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1)))),D$8-1)<>"")+0)+1,SUM($B$3:$B$6))=0,SUM($B$3:$B$6),MOD(SUMPRODUCT((OFFSET($C$9,MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1)))),1,MIN(IF($C9:$C$15="",ROW($C9:$C$15)-ROW($C$8)-1))-MAX(IF($C$8:$C9="",ROW($C$8:$C9)-ROW(INDEX($C$8:$C9,1,1)))),D$8-1)<>"")+0)+1,SUM($B$3:$B$6))),$C$2:$C$6,1)+1))))

Fill down and right and it should treat each group separated by a blank row as a unique group. Take a look at the attachment to see if it'll work for you: