+ Reply to Thread
Results 1 to 15 of 15

Determine number of batch based on product type

  1. #1
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Determine number of batch based on product type

    Hi,

    I would like to determine number of batches based on product type and its lot size. The products that can form a batch must have same product type and their total sizes cannot exceed the batch capacity.

    The example is per attachment. I try to determine the number of batch based on total sizes of the product types divide by it capacity, but it not always work. If anyone know a better way please let me know.
    Attached Files Attached Files

  2. #2
    Forum Guru teylyn's Avatar
    Join Date
    10-28-2008
    Location
    New Zealand
    MS-Off Ver
    Excel 365 Insider Fast
    Posts
    11,364

    Re: Determine number of batch based on product type

    Hello,

    can you explain why the result should be 3? If the capacity is 11 and the total size is 21, then that's two batches, one not quite full.

  3. #3
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Determine number of batch based on product type

    Try
    =COUNTIFS($D$4:$D$9,"="&G4,Job_Size,"<="&G5) - Place into G7 and copy across
    Attached Files Attached Files
    Last edited by smuzoen; 02-15-2012 at 07:20 AM.
    Hope this helps.
    Anthony
    Pack my box with five dozen liquor jugs
    PS: Remember to mark your questions as Solved once you are satisfied. Please rate the answer(s) by selecting the Star in the lower left next to the Triangle. It is appreciated

  4. #4
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    Quote Originally Posted by teylyn View Post
    Hello,

    can you explain why the result should be 3? If the capacity is 11 and the total size is 21, then that's two batches, one not quite full.
    Because split of product to differnt batch is not allowed. Even though total size is 21, but sum of any two products with type A will exceed the batch capacity.

  5. #5
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    Anthony, it will be wrong if the size of product 1 is 2. As product 1 (2 lots) and product 5 (8 lots) can be processed in same batch, so the no of batches should be 2 instead of 3.

    I need a formula that will work in both situations.

  6. #6
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Determine number of batch based on product type

    How about a VBA solution - run macro called calBatch and see if you get the results you expect (if I understand your logic rules correctly)
    Please Login or Register  to view this content.
    Some bright spark may be able to give you a formula answer.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    Anthony,

    Thanks for your reply. Is it something wrong with the macro? I click run but nothing happen.

  8. #8
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Determine number of batch based on product type

    I have added clearing the calculated values and adding a msgbox to show that the macro works. Unless you change the values in the Data table (Job Size, Type) then you will get the same answer every time the macro is run. Change the values in the Data table and run the macro - the message box will confirm that the macro runs. You can remove the message box once you are happy the macro is executing to your specifications as it will be annoying. Make sure that you enable macros as well when you open the workbook otherwise the macro will not run.
    Attached Files Attached Files

  9. #9
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Determine number of batch based on product type

    -delete - duplicate-server timeout

  10. #10
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    Dear Anthony,

    It doesn't work out as what i expected. See the attachment for the modified input.

    The objective is to determine "minimum" number of batches that need to form for each product type. Constraint: the total sizes of the product (from a same type) in a batch should not exceed the type's capacity. e.g. as product 3 (5 lots)+ product 5 (3 lots) > 7, so that cannot in a batch, same go for product 6 (6 lots) + product 5 (3 lots) > 7. therefore the total batches required is 3 instead of 2.

    For type C, since no product from this type, so the number of batch should be 0.

    I hope this is clear to you.
    Attached Files Attached Files
    Last edited by sc11; 02-15-2012 at 11:18 PM.

  11. #11
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    I have some sort of figure out a workable procedure to calculate the minimum number of batch based on product types and job sizes (within capacity) as per the attaced docx, but i need help on programming the procedure to a VBA macro that could run in excel.

    The input data is per the attached excel file. The solution of the macro should be reflect in column H7:J7.

    If you know a better way to do so, please share with me.
    Attached Files Attached Files

  12. #12
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Determine number of batch based on product type

    I am still working on a solution for you. I don't think you appreciate how technically complicated this is to do. Essentially you need to find every possible sum combination that either equals the capacity or sum as close as possible to capacity without being greater than capacity. I am having to use a recursive procedure to do this which I have been able to do with the help of some code written by someone very clever. I attempted to use the Solver Add In however I could not get it to give me the results I need by using binary constraints method.
    I will post something for you soon unless someone else picks this up. Your suggested solution while being a consideration will not give you the correct result.
    As I say this is a very complex problem. The recursive solution is very fast however when finalised how large will the data set be?
    As an example what you are asking is to test every combination of a set of numbers to see if it sums less than or equal to a number. To follow is a succinct VB.net example that does not take into consideration the constraints you are requesting - this simply calculates every sum possibility for an array - remember that the sum can be generated from 1 number or all numbers so coding for the conditions is a challenge. Just google subset sum problem and you will appreciate the complexity of your request.
    This is certainly a challenge.
    Remember to follow is a VB.net sample and certainly not the solution but is part of the solution
    Please Login or Register  to view this content.
    Last edited by smuzoen; 02-16-2012 at 12:07 PM.

  13. #13
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    Anthony,

    Thanks for your reply and few attempts that you did. I really appreciate it. Indeed, it is complicated thing, i attempted to solve it by myself for quite sometimes already. It is a pre-solve for my scheduling problem, just found out the previous method got error recently. By now, i'm sure you fully understand what I'm looking for.

    While waiting for other to help, i never stop thinking, and i thought that i had come out a clever idea yesterday. So, it still not perfect, isn't? If someone could pragram my idea into a macro, i would like to test it out more thoroughly. Sorry, I never thought that this will upset you.

    The products involve will be around 20 with 7 types of products.

    I'm looking forward for your workable solution. Thank you.

    Regards,
    SC11
    Last edited by sc11; 02-16-2012 at 07:38 PM.

  14. #14
    Valued Forum Contributor smuzoen's Avatar
    Join Date
    10-28-2011
    Location
    Brisbane, Australia
    MS-Off Ver
    Excel 2003/2007/2010
    Posts
    610

    Re: Determine number of batch based on product type

    Sorry, I never thought that this will upset you.
    NO no no - by no means I am upset - It is sometimes hard to express your intended tone in posts - I was only pointing out the Subset Sum Problem is a complex algorithm - I hopefully have nearly finished completing the code. I see this as a challenge. I am sorry if you thought I was having a go at you. I never intended to express myself that way. I was only trying to display the complexity of something that actually on the surface appears to be relatively straight forward. I enjoy a challenge and this certainly is a challenge. If memory serves me correctly this type of problem was actually proposed as a coding challenge on an Excel Website (that will go unnamed) a few years ago.
    So no I am not upset by any means- I have posted my code to date -I am trying to use a recursion solution - it is not finished however it may further display how complex the problem is. See attached workbook.
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by smuzoen; 02-16-2012 at 11:03 PM.

  15. #15
    Registered User
    Join Date
    11-24-2011
    Location
    malaysia
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Determine number of batch based on product type

    Quote Originally Posted by smuzoen View Post
    I was only trying to display the complexity of something that actually on the surface appears to be relatively straight forward. I enjoy a challenge and this certainly is a challenge. If memory serves me correctly this type of problem was actually proposed as a coding challenge on an Excel Website (that will go unnamed) a few years ago.
    Yeah, total agree with you. I also overlook the problem thought that it is relatively straight forward, which can solve by excel equation. Now, i'm stuck with it, as i'm not familier with Macro programming. The codes are so complicated, i could never done it by myself.

    Thanks for taking up the challenge, i'm glad that someone is helping me on this. I hope to hear good news from you soon.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1