Excel: Splitting of data based on calculation Solved/Closed

Question

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

rizvisa1 · Answer

Could you explain the first two lines of your intend
I was unable to get the answer that u had in your sample file

wliang · Answer

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

wliang · Answer

Good day,
Yes, the intended result shall be created as a result of the macro.

Thanks in advance for your help.

Best regards,
wliang

Excel: Splitting of data based on calculation

3 responses

Similar discussions

Subscribe To Our Newsletter!