# Determine number of batch based on product type

1. ## 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.

2. ## 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. ## 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

4. ## Re: Determine number of batch based on product type

Originally Posted by teylyn
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. ## 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. ## 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.

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

9. ## Re: Determine number of batch based on product type

-delete - duplicate-server timeout

10. ## 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.

11. ## 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.

12. ## 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.``

13. ## 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

14. ## 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.``

15. ## Re: Determine number of batch based on product type

Originally Posted by smuzoen
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.

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

#### 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