Creating packages with a maximum limit in a column | Excel 2016

%3CLINGO-SUB%20id%3D%22lingo-sub-1511015%22%20slang%3D%22en-US%22%3ECreating%20packages%20with%20a%20maximum%20limit%20in%20a%20column%20%7C%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1511015%22%20slang%3D%22en-US%22%3E%3CP%3EHello!%3C%2FP%3E%3CP%3E%3CSPAN%3EI%20want%20to%20create%20Packages%20with%20a%20maximum%20limit.%20You%20can%20think%20of%20a%20transport%20pallett%20and%20the%20weight%20of%20a%20pallett%20is%20not%20allowed%20to%20have%20a%20weight%20over%201%20kg.%20Now%20i%20have%20my%20excel%20list%20with%20a%20column%20for%20my%20package%20number%20and%20the%20weight%20of%20the%20package.%20We%20don't%20have%20to%20worry%20about%20the%20size%20and%20so%20on.%20Only%20the%20weight%20is%20interesting%20for%20us.%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAs%20an%20Example%3A%3C%2FP%3E%3CP%3E01%20-%200%2C35kg%3C%2FP%3E%3CP%3E02%20-%200%2C65kg%3C%2FP%3E%3CP%3E04%20-%201%2C00kg%3C%2FP%3E%3CP%3E05%20-%200%2C23kg%3C%2FP%3E%3CP%3E06%20-%200%2C18kg%3C%2FP%3E%3CP%3E07%20-%200%2C27kg%3C%2FP%3E%3CP%3E08%20-%200%2C36%20kg%3C%2FP%3E%3CP%3E09%20-%200%2C21kg%3C%2FP%3E%3CP%3E10%20-%200%2C21kg%3C%2FP%3E%3CP%3E11%20-%200%2C21kg%3C%2FP%3E%3CP%3E12%20-%200%2C21kg%3C%2FP%3E%3CP%3E20%20-%200%2C48kg%3C%2FP%3E%3CP%3ENow%20i%20want%20Excel%20to%20create%20packages%20for%20me.%20Like%20Number%201%20and%202%20is%20one%20package.%20Number%204%20is%20one%20package.%20And%20so%20on.%3C%2FP%3E%3CP%3EI%20would%20be%20very%20happy%20about%20every%20help!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20very%20much%20in%20advance!%3C%2FP%3E%3CP%3EJonas%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1511015%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1516388%22%20slang%3D%22en-US%22%3ERe%3A%20Creating%20packages%20with%20a%20maximum%20limit%20in%20a%20column%20%7C%20Excel%202016%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1516388%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F723250%22%20target%3D%22_blank%22%3E%40JonasBer%3C%2FA%3E%26nbsp%3B%2C%20this%20is%20not%20an%20easy%20problem%20to%20solve.%20This%20is%20a%20variation%20of%20the%20classic%20Knapsack%20problem%20(%3CA%20href%3D%22https%3A%2F%2Fen.wikipedia.org%2Fwiki%2FKnapsack_problem%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EKnapsack%20problem%20-%20Wikipedia%3C%2FA%3E).%20After%20a%20quick%20search%2C%20I%20found%20a%20couple%20of%20solutions%20you%20can%20try%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere's%20a%20VBA%20solution%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.excel-easy.com%2Fvba%2Fexamples%2Fknapsack-problem.html%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3EKnapsack%20Problem%20in%20Excel%20VBA%20-%20Easy%20Excel%20Macros%3C%2FA%3E%3C%2FP%3E%3CP%3EAn%20Excel%20solution%20%3A%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Fwww.jonaslindstrom.dk%2F%3Fp%3D280%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3ESolving%20the%200-1%20Knapsack%20problem%20with%20Excel%20%26amp%3B%238211%3B%20Jonas%20Lindstr%C3%B8m%3C%2FA%3E%3C%2FP%3E%3CP%3EAn%20add-in%20that%20seems%20to%20help%20(I%20tried%20the%20Trial%20and%20looks%20promising)%20%3A%26nbsp%3B%3CA%20href%3D%22http%3A%2F%2Fwww.summatch.com%2FAdmin_Pages%2FHome.aspx%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3E%20Excel%20SumMatch%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20these%20get%20you%20started.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Frequent Visitor

Hello!

I want to create Packages with a maximum limit. You can think of a transport pallett and the weight of a pallett is not allowed to have a weight over 1 kg. Now i have my excel list with a column for my package number and the weight of the package. We don't have to worry about the size and so on. Only the weight is interesting for us.

As an Example:

01 - 0,35kg

02 - 0,65kg

04 - 1,00kg

05 - 0,23kg

06 - 0,18kg

07 - 0,27kg

08 - 0,36 kg

09 - 0,21kg

10 - 0,21kg

11 - 0,21kg

12 - 0,21kg

20 - 0,48kg

Now i want Excel to create packages for me. Like Number 1 and 2 is one package. Number 4 is one package. And so on.

I would be very happy about every help!

 

Thank you very much in advance!

Jonas

1 Reply
Highlighted

@JonasBer , this is not an easy problem to solve. This is a variation of the classic Knapsack problem (Knapsack problem - Wikipedia). After a quick search, I found a couple of solutions you can try:

 

Here's a VBA solution: Knapsack Problem in Excel VBA - Easy Excel Macros

An Excel solution : Solving the 0-1 Knapsack problem with Excel – Jonas Lindstrøm

An add-in that seems to help (I tried the Trial and looks promising) :  Excel SumMatch

 

Hope these get you started.