• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code to breakdown numbers in a sequence

B

bluetaurean

Member
Can someone help me with code to breakdown qtys into number of runs. example product A having a total qty of 75K needs to be broken down in 3 runs of 25K each but the output list must do a sequence of each product once before looping to back to the first product.


Input
Product Qty Run Size No of Runs
A 75,000 25,000 3.0
B 75,000 30,000 2.5
C 45,000 30,000 1.5

Output needed


Product




Runs
A 25,000
B 30,000
C 30,000
A 25,000
B 30,000
C 15,000
A 25,000
B 15,000
C -
 
Thanks @Debraj & @NARAYANK991 for both type of solution.
After reading the original post says "code" I got stuck as I am not that good in VBA.

I am going to keep both the solution in my personal collection.

Thanks Again,amazing work.

Regards,
 
Thanks @Debraj & @NARAYANK991. Exactly the solution needed. For the VBA solution, is it possible to introduce a standard breaktime (changeover time) say a 2hrs after each change. noted one unit is qty and another is hours but i can modify the code if i know the basic structure. so the example above the output will be

A 25,000
Break 2
B 30,000
Break 2
C 30,000
Break 2
etc

thanks again.
 
Hi Narayan,

My original query should have been in a common unit.

Product Qty Rate/Hr MaxHrsPerRun Hours required
A 175000 2000 40 88
B 48000 2000 40 24
C 315000 3000 35 105

Output

Product Hrs
A 40
Changeover 4
B 24
Changeover 4
C 35
Changeover 4
A 40
Changeover 4
C 35
Changeover 4
A 8
Changeover 4
C 35
Changeover 4

Thanks your help
 
Hi ,

There is some confusion ; there are 4 quantities involved here :

1. Quantity
2. Rate per hour
3. Maximum hours per run
4. Hours required

The Hours required is a calculated value = Quantity / Rate per hour , rounded up to the next whole number.

I do not understand what the third quantity viz. Maximum hours per run is doing.

What quantities should appear on the output sheet ?

Narayan
 
Hi Narayan,

the Maximum hours per run is using a similar logic as max qty per run. As the change over time is in hours and cannot be converted to qty to show in the same output column, i converted the max qty per run into equivalent number of hours to have a common unit in the output range. i.e. if 40000 is the max qty per run which takes 20 hours to produce, the output range will show the split as 20 hours followed by a changeover of 4 hours. the numbers are derived from similar logic : Max qty per run divided by rate per hour = Max hours per run.

thanks
 
Hi ,

The problem is the sudden change of terminology ; in your first post you had mentioned Run Size ; can I assume that the Maximum Qty. per Run is the same as Run Size ?

If so , then Run Size / Rate per Hour = Hours Required for each Run.

Also , Quantity / Rate per Hour = Total Number of Hours

Is this correct ?

Also , can you say which quantities will be present in the data ? Can I assume :

1. Product Quantity
2. Rate per Hour
3. Hours per Run

The Total Hours Required will be a calculated field.

What is output will be :

1. Product Name
2. Hours per Run

followed by the constant changeover break time.

Is this OK ?

Narayan
 
You must log in or register to reply here.
Back
Top