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.

Try
=COUNTIFS($D$4:$D$9,"="&G4,Job_Size,"<="&G5) - Place into G7 and copy across

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.

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.

-delete - duplicate-server timeout

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.

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.

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

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.