Previous Topic Next Topic

Excel: Splitting of data based on calculation

Solved/Closed
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Jun 19, 2012 at 03:43 AM
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014 - Jun 30, 2012 at 08:33 AM
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
Related:

3 responses

Answer 1 / 3
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 25, 2012 at 08:20 PM
Could you explain the first two lines of your intend
I was unable to get the answer that u had in your sample file
Answer 2 / 3
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Jun 27, 2012 at 09:00 AM
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
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jun 28, 2012 at 06:50 PM
so this "intended result" would be a new sheet that would be created as a result of macro ?
Answer 3 / 3
wliang Posts 41 Registration date Thursday June 16, 2011 Status Member Last seen May 7, 2014
Jun 30, 2012 at 08:33 AM
Good day,
Yes, the intended result shall be created as a result of the macro.

Thanks in advance for your help.

Best regards,
wliang

Similar discussions

Date formatting in dd/mm/yyyy
nisha -
appu -

11 responses
How to calculate positions (1st,2nd,3rd...) in Excel
hardy -
TrowaD -

6 responses