# Find best combination of numbers to meet a goal

• My company makes pieces of tubing from our standard stock lengths of 20ft, 40ft and 60ft. We get a daily order request of tubing pieces of different lengths (less than 20ft) and my job is to figure out what combination of individual pieces can be combined to make up a stock piece with minimal waste (we ideally want to use 40ft stock length tubing to cut up for orders as it is easy to handle.)

I have a numeric list that represent lengths of piece order (in mm). The number of pieces in the order changes all the time. I need to find the combination of numbers that comes closest to 12,192mm (40ft). Then once I find that, I need to discard those piece orders (as they have been used to make up a stock piece) and then I need to find the next combination, etc until all pieces in the order have been accounted for. If there is a great deal of waste (ie more than 15%) the combination is not ideal and I would want to try to fit pieces to 18,288mm (60ft) or 6,096mm (20ft)

For example the list below is an order, each row is a piece order request in mm [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64, align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3694[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3131[/TD]

[/tr]

[tr]

[TD="align: right"]3131[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]2253[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]4402[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]1174[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]2254[/TD]

[/tr]

[tr]

[TD="align: right"]3225[/TD]

[/tr]

[tr]

[TD="align: right"]4402[/TD]

[/tr]

[tr]

[TD="align: right"]4402[/TD]

[/tr]

[/TABLE]

I could get these broken out into the following groupings: [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 76"]ACTUAL LENGTH[/TD]
[TD="width: 329"]Combinations used from list above[/TD]

[/tr]

[tr]

[TD="align: right"]11333 > [/TD]

[td]

2254, 3694, 2254,3131

[/td]

[/tr]

[tr]

[TD="align: right"]11552 > [/TD]

[td]

2254,2254,1174,1174,1174,1174,1174,1174

[/td]

[/tr]

[tr]

[TD="align: right"]11835 > [/TD]

[td]

3225,3131,2254

[/td]

[/tr]

[tr]

[TD="align: right"]11057 > [/TD]

[td]

4402,4402,2253

[/td]

[/tr]

[tr]

[TD="align: right"]10852 > [/TD]

[td]

3225,3225,4402

[/td]

[/tr]

[/TABLE]

I tried using the SOLVER add-in but couldn't get it to work so wondering if there is another way or in vb script to find what I need it to do?

• Any ideas from anyone out there?

• Hi,

To accomplish exactly what you are after is quite difficult as you have a lot of variables to deal with.

As it was a typical June rainy day yesterday here in the UK and I like a challenge thought I would have a stab at trying to do this.

My solution uses a SQL cross join query. A cross join (or cartesian join) is used when you wish to create combination of every row from two or more tables. All row combinations' are included in the result. The tables used for the cross join are just a copy of the order data. You have to specify the number of lengths you want to cut from a stock pieces i.e. in my code below it is 4 so 4 tables are create and the SQL cross join query is...

SQL
``SELECT * FROM [B:B], [C:C], [D:D], [E:E]``

The solution is not 100% perfect as some of the order lengths don't fit a generated combination. But I guess you could always try running it with different parameters or work out the remaining lengths yourself. You don't want to do yourself completely out of your job... :smile:

I have attached an example 2016 Workbook 'Best Combinations.xlsm' with the working code.

Regards,

Tom Rowe...

VBA Code Follows...

## Files

• Tom you are a gentleman and a scholar! I tried re-running the code an got an error on the sorting, but I can try to figure that out myself.

One thing I failed to mention which I did not think was important at the time was that each row of material order has an identifier related to the job the piece is needed for. So when I give my boss the best combinations he wants to know which job the pieces are related to. [TABLE="border: 0, cellpadding: 0, cellspacing: 0"]

[tr]

[TD="width: 64"]job#[/TD]
[TD="width: 64"]length[/TD]

[/tr]

[tr]

[td]

r1

[/td]

[TD="width: 64"]2254[/TD]

[/tr]

[tr]

[td]

r4 r5 r3

[/td]

[TD="width: 64"]3694[/TD]

[/tr]

[tr]

[td]

r6

[/td]

[TD="width: 64"]2254[/TD]

[/tr]

[tr]

[td]

r7

[/td]

[TD="width: 64"]3225[/TD]

[/tr]

[tr]

[td]

r7

[/td]

[TD="width: 64"]3225[/TD]

[/tr]

[tr]

[td]

r8

[/td]

[TD="width: 64"]3131[/TD]

[/tr]

[tr]

[td]

r9

[/td]

[TD="width: 64"]2254[/TD]

[/tr]

[tr]

[td]

r9

[/td]

[TD="width: 64"]2254[/TD]

[/tr]

[tr]

[td]

r10

[/td]

[TD="width: 64"]1174[/TD]

[/tr]

[tr]

[td]

r10

[/td]

[TD="width: 64"]1174[/TD]

[/tr]

[tr]

[td]

r10

[/td]

[TD="width: 64"]1174[/TD]

[/tr]

[tr]

[td]

r10

[/td]

[TD="width: 64"]1174[/TD]

[/tr]

[tr]

[td]

r10

[/td]

[TD="width: 64"]1174[/TD]

[/tr]

[tr]

[td]

r10

[/td]

[TD="width: 64"]1174[/TD]

[/tr]

[tr]

[td]

r11

[/td]

[TD="width: 64"]3225[/TD]

[/tr]

[tr]

[td]

r11

[/td]

[TD="width: 64"]3225[/TD]

[/tr]

[tr]

[td]

r12

[/td]

[TD="width: 64"]3131[/TD]

[/tr]

[tr]

[td]

r13

[/td]

[TD="width: 64"]2254[/TD]

[/tr]

[tr]

[td]

r15

[/td]

[TD="width: 64"]4402[/TD]

[/tr]

[tr]

[td]

r15

[/td]

[TD="width: 64"]4402[/TD]

[/tr]

[tr]

[td]

r15

[/td]

[TD="width: 64"]4402[/TD]

[/tr]

[tr]

[td]

r16

[/td]

[TD="width: 64"]2253[/TD]

[/tr]

[/TABLE]

Currently the best combination is put in a comment box (Combination: [3225] [4402] [2254] [2254]), but how would I be able to have that data listed in a cell with the job# included (ie Combination: [3225] [4402] [2254] [2254] r7, r15, r1, r6) or one column for length combination and one column for job #?

• Tom I have attached a summary to easily see what I am talking about as it may not be super clear above. Thanks again

## Files

• Hi,

Don't know what's causing the error for you as the sorting works fine for me. Did you delete the X's from the Order column before you ran it which could cause problems ? If you can post a debug of the code where it is failing I will have a look to see if I can replicate it or add some error handling to get more information. Also what version of Excel are you using which can cause problems if different from mine which is Office 365 Excel 2016.

It should be relatively simple to add the job Id as that would be done last when you check what order length matches what combination. I was also thinking it would be better to make the subroutine AddBestCombinationRowToOrder that does that more generic so you can pass a worksheet object to it specifying what Worksheet you want the order on rather than being limited to wsOrder.

Regards,

Tom

• Hey Tom
I am using Windows 7, Excel 2013
I did remove the X but it was sometimes failing at line

Code
``wsOrder.Sort.SortFields.Add2 Key:=Range("B1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal``

I would just close the book, not save it, reopen and run it again and it would be fine.

I am still sorting through your code to figure out how you determine if rngFoundCell is not nothing (blnCombinationMatched = True in essence).I am decent with arrays but the ranges not so much.

I figure at that point in the loop I could have used the information you put in a comment box to put in cells in a column (and then delete those values if blnCombinationMatched = False). Unless I do it after the loop before the sort?

• Hi,

rngFoundCell is not nothing is determined by the .Find which returns nothing if the What:= item is not found.

I have updated the code for you to handle the Job #. Also the code is now more generic so you can pass a worksheet object to the subroutinest specifying what Worksheet you want. I also found a minor bug with the sort routine in that it was using the wrong worksheet to set the Last Row on this line...

Code
``.SetRange Range("A2:C" & Trim(Str(LastRow(objOrderWorksheet, 1))))``

Which may have been causing your problem.

I have attached an updated workbook for you.

Regards,

Tom Rowe...

The updated VBA Code follows...

## Files

• Hi Tom,
Thank you for the update. I revised the summary a bit to fit my bosses needs, but ran into an issue when I tried my first order. Basically some job #s have length quantities greater than 1, so I broke out the quantity into duplicate rows (ie Qty 2 of length 6071 would break out into 2 rows of 6071). Therefore if I delete the code that removes duplicates it would work. Is there any harm in removing that section?:

Code
``````' Remove the duplicate combinations.
wsBestCombinations.Columns("A:A").Select
wsBestCombinations.Range("A1:C" & LastRow(wsBestCombinations, 1)).RemoveDuplicates Columns:=1, Header:=xlYes``````

Also, in the attached file, I ran the code with the job #. Is there any easy way to group the 'order combination' tab so that duplicate order combinations & job#s show up as one light with a quantity?

## Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!