How to optimise material use with VBA and multiple solvers

[ZPar]

Hello guys,
I got the following problem.
I need to calculate the minimum required aluminum bars and cut them in predetermined lengths.
The problem is the following:
Constraints
1: My aluminum bars are 6m long
2: Every time I cut, 4mm of material is lost.
Let's say that I have the following requirements
6000mm - Qnty: 1
5400mm - Qnty: 1
3770mm - Qnty: 2
2450mm - Qnty: 4
1255mm - Qty: 7
550mm - Qty: 3
I would create a table with 3 columns
Any ideas, anyone?
Thanks

 

[felixstraube]

Hi, I'm a little late but maybe it will be useful to you.
Here is a working file:

Bars.zip

It just uses formulas. And it is too big to past de whole xl2bb
Here is the first columns:

Bars.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1		Bar length [mm]	6000		Max length used of 1 bar	6000
2					Theoretical min # of bars	7
3					# of bars needed	8							7	7	7	7	7
4											Calculation
5		# of pieces	18										1	2	3	4	5
6		Requirements			Sorted result			Unsorted result					6	5	7	7	6
7		Length [mm]	Qty		Piece length	Bar #		Piece length	Bar #			Long with cuts	12846	15528	9774	12459	18687
8		6000	1		2450	1		6000	6		1	6000	6	5	3	2	1
9		5400	1		1255	1		5400	2		2	5404	4	3	7	1	7
10		3770	2		5400	2		3770	8		3	3774	7	2	3	7	6
11		2450	4		550	2		3770	7		4	3774	6	2	2	6	6
12		1255	7		2450	3		2450	3		5	2454	3	2	2	7	6
13		550	3		1255	3		2450	5		6	2454	7	4	5	6	6
14					2450	4		2450	1		7	2454	2	4	4	3	5
15					1255	4		2450	4		8	2454	4	2	7	6	6
16					1255	4		1255	1		9	1259	6	4	6	6	6
17					2450	5		1255	4		10	1259	4	4	5	6	7
18					1255	5		1255	7		11	1259	2	4	5	7	6
19					550	5		1255	5		12	1259	3	6	5	6	4
20					6000	6		1255	4		13	1259	1	2	4	4	6
21					3770	7		1255	3		14	1259	7	2	6	5	7
22					1255	7		1255	8		15	1259	6	6	1	1	3
23					550	7		550	5		16	554	6	2	7	7	1
24					3770	8		550	7		17	554	1	6	7	4	3
25					1255	8		550	2		18	554	4	3	5	3	5
Sheet1

Cell Formulas
Range	Formula
F1	F1	=MAX(BYROW(UNIQUE(I8#), LAMBDA(x, SUM($L$8:$L$100*(I8#=x)))))
F2	F2	=ROUNDUP(SUM(L8#)/$C$1,0)
F3	F3	=COUNT(UNIQUE(I8#))
M3:NUB3	M3	=INT(SEQUENCE(,10000,0)/(10000/4))+$F$2
C5	C5	=SUM(C8:C25)
M5:NUB5	M5	=SEQUENCE(,10000)
M6:Q6	M6	=COUNT(UNIQUE(M8#))
M7:Q7	M7	=MAX(BYROW(UNIQUE(M8#), LAMBDA(x, SUM($L$8#*(M8#=x)))))
E8:F100	E8	=LET(d, SORT(H8:I100,2), IF(d=0,"",d))
H8:H25	H8	=LET(l,NUMBERVALUE(TEXTSPLIT(CONCAT(REPT(B8:B25&";",C8:C25)),,";",1)), l )
I8:I100	I8	=LET(b, INDEX(M8:NUB100,,MATCH(LET(d, (M7:NUB7<=C1)*M6:NUB6, MIN(IF(d = 0, "", d)))&";1", M6:NUB6&";"&(M7:NUB7<=C1)*1,0)), IF(b=0, "",b))
K8:K25	K8	=SEQUENCE(SUM(C8:C25))
L8:L25	L8	=LET(l,NUMBERVALUE(TEXTSPLIT(CONCAT(REPT($B$8:$B$25&";",$C$8:$C$25)),,";",1)), lwc,IF(l<($C$1-4),l+4,$C$1), lwc )
M8:Q25	M8	=RANDARRAY($C$5, , 1,M3,TRUE)
Dynamic array formulas.

there are 10000 columns for the calculation.
Basically we generate random combination and check the minimum number of bar needed.
You'll have to press F9 a few time to see if the "# of bars needed" (cell F3) becomes smaller.

For your example data with less than 5 F9 presses you'll get the result of 8 bars. Which i think is the smallest amount of bars needed.
The result in cells E8:F8 and downwards may not have all numbers from 1 to 8, there may be 1 to 10 and a 3 or some other number missing. Thats because they are the random generated numbers.

One example:

Piece length	Bar #
2450	1
2450	1
2450	2
1255	2
1255	2
550	2
3770	4
1255	4
550	4
1255	5
1255	5
550	5
5400	7
6000	8
3770	9
1255	9
2450	10
1255	10

Here you can see that the bar sequence is from 1 to 10 but the 3 and the 6 are missing.
We could see to change the numbers so they would be from 1 to 8 (if 8 is the maximum number of bars needed).

Let me know if it works for you.