• 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.

Insert Change time based on a change matrix

B

bluetaurean

Member
Can someone please help with a VBA code for this

a. there is a list of product running for certain hours in a sequence
b. for each product change, the change over time is listed in a matrix. eg. Orange to Mango 2 hrs.
c. i need a output list which takes the original list and inserts a row and hours required based on a table.
the list of product running will be dynamic so a vba code will be useful.

please help. file attached for reference.
 

Attachments

  • changetime.xlsx
    8.6 KB · Views: 7
Thanks David. this solution requires the output table to created manually and then input of a formula in the change over cells.
What i am trying to do is create the output table via a code. take the first element from the first "Plan" table, put it in the output table, the change over time based on what is following and then the 2nd element in the plan table and so on.

the actual plan table will contain more than 50 elements and creating the output table manually is cumbersome.
 
thanks Narayan,

the formula works well. One of the reason for asking a VBA solution was that the original plan it self is derived with a script based on certain other constraints like min/max constraints, run sizes etc. as a further refinement to that script i was adding this part of change-overs. so if you can help with a vba solution that will be great.
 
Hi ,

Can we have the change times in the form of a matrix , rather than a 3 column linear range ?

With 50 elements , entering 2500 ( 50 x 50 ) rows of data will be more cumbersome than entering the same data in a matrix.

Narayan
 
Hi Narayan,
Sure a matrix is good (better). i put it out in a linear manner assuming this would be easier. Updated file with the the matrix. Appreciate your help.
 

Attachments

  • changetime.xlsx
    12.6 KB · Views: 3
Hi bluetaurean,

This layout makes more sense... Try this in G22:
=SUMIFS($H$3:$H$18,$F$3:$F$18,$F22,$G$3:$G$18,G$21)

Copy across and down.

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