Excel: Splitting of data based on calculation [Solved/Closed]

Report
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014
-
Good day,

I would like to seek help to how to split and populate data based on the result of certain calculation.

Here are the criteria for the calculation.

1. The daily work hour is split into a 20-minutes interval with the starting time at 7:30am.

2. Time to complete one piece of goods is X seconds.

Use the above as the basis, here is an example. The time to complete one piece is 4.5secs, which means in 20 minutes, we can complete 266pcs. I have a few products to be completed, such as product A 120pcs, product B 300pcs, product C 80pcs and product D 500pcs.

With this, we can complete product A 120pcs and only 146pcs of product B in the first 20-minutes interval. Balance of product B of 154pcs will need to be completed in the second 20-minutes interval together with 80pcs product C and 32pcs product D. In the third 20-minutes interval, 266pcs product D can be completed and then in the fourth 20-minutes interval, balance 202pcs of product D can be completed.

I will need to sequence and populate the data as in the scenario above.

I have attached a sample file with the data and intended result as in the following link.

http://speedy.sh/ukCCZ/SEQ-PLAN.xls

Appreciate the help from everyone on this.

Thank you.

Best regards,
wliang

3 replies

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
Could you explain the first two lines of your intend
I was unable to get the answer that u had in your sample file
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014

Good day rizvisa1,

Referring to your questions, here is what I am facing. We have break down the daily work hours into 20-minutes intervals. The production time for 1pc is 4.5 seconds, which means we can produce 266pcs in 20 minutes. Thus, we need to break down the daily plan into groups where each group is of 20-minutes intervals and the quantity can be produced cannot exceed 266pcs. If the quantity of an item exceeded 266pcs, the balance needs to go into the next group of 20-minutes interval.

In the attached file, I have provided the result expected from the data.

Hope this clears the doubt.

Thank you in advance for your help.

Best regards,
wliang
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
so this "intended result" would be a new sheet that would be created as a result of macro ?
Posts
41
Registration date
Thursday June 16, 2011
Status
Member
Last seen
May 7, 2014

Good day,
Yes, the intended result shall be created as a result of the macro.

Thanks in advance for your help.

Best regards,
wliang