1D Optimization Using VBA

[cmondeau]

I am trying to create a Macro to optimize the lengths of one dimensional (1D) stock pipe using VBA and Solver. Hopefully any methods discussed in this thread will address not only my inquiries, but to the rest of the forum as well.

In an attempt to keep this thread organized, here is a link to my research, criteria, and other pertinent information towards the development of this code.

https://www.evernote.com/shard/s286...b492e95ce182/e5ec2c4e0bae3b5465321251966b5d86

Disclosure: I am a firm believer in the sharing of knowledge without constraint, much like Germany and it's recent educational reform . Also, if it seems I am being nit-picky about complicated code, it's because I would like to learn, so any basic or simplified code, with lots of 'comments and explanations' next to your code is greatly appreciated! I am not asking for a hand-out, but would really like to understand the mechanics in the process of developing a productive tool. And if I'm out of line, slap me.

Here is a selection of data from a spool order for our pipe. This is what I've been using while developing a macro for the optimization macro. This contains a wide variety of the types of pipe needed for cutting (different sizes, lengths, materials). For myself, I will not be editing the data repot. I would simply like to pull my report from the part run, open Excel, press a lovely little "Solve" button, and have the Macro give me a report.

[TABLE="width: 320"]
<tbody>[TR]
[TD="class: xl63, width: 64"]Item No[/TD]
[TD="class: xl63, width: 64"]Pipe Size[/TD]
[TD="class: xl63, width: 64"]Length[/TD]
[TD="class: xl63, width: 64"]Description[/TD]
[TD="class: xl63, width: 64"]Type[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1284[/TD]
[TD="class: xl63"]0 1/2"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1252[/TD]
[TD="class: xl63"]0 1/2"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1162[/TD]
[TD="class: xl63"]0 1/2"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1429[/TD]
[TD="class: xl63"]0 3/4"[/TD]
[TD="class: xl63"]3"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1276[/TD]
[TD="class: xl63"]0 3/4"[/TD]
[TD="class: xl63"]2"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1269[/TD]
[TD="class: xl63"]0 3/4"[/TD]
[TD="class: xl63"]3"[/TD]
[TD="class: xl63"]A53 ERW Gr-B Std Wt Blk Stl Pipe PE[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1251[/TD]
[TD="class: xl63"]1"[/TD]
[TD="class: xl63"]7"[/TD]
[TD="class: xl63"]A53 CW Sch 40 Blk T&C[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1157[/TD]
[TD="class: xl63"]1"[/TD]
[TD="class: xl63"]12"[/TD]
[TD="class: xl63"]A53 CW Sch 40 Blk T&C[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
[TR]
[TD="class: xl63, align: right"]1247[/TD]
[TD="class: xl63"]1"[/TD]
[TD="class: xl63"]12"[/TD]
[TD="class: xl63"]A53 CW Sch 40 Blk T&C[/TD]
[TD="class: xl63"]Straights[/TD]
[/TR]
</tbody>[/TABLE]

Again, this is only a snippet of a spool list, so I've been using dynamic ranges and such things (along with butterflies, sunshine, and AK-47's).

O.K. I think that's about it. As always any and all thoughts are welcomed, and I'll try my best to try anything and understand it. Thanks for your consideration!

 

[cmondeau]

Here are the steps I've taken so far in my journey:

1. Keep the raw data in "Sheet1" unhampered. I would like to keep this feature, and add only a command button to run the macro that sorts, optimizes, and templates the data.
2. In a second sheet create a simplified and dynamic data table/matrix/array, much like the ones (and code) posted in this previous thread: http://www.mrexcel.com/forum/excel-...generate-table-visual-basic-applications.html
3. Made coding that generated new sheets after the dynamic table to pull information from and fill a template to run Solver through. This was the step before trying to loop solver in VBA: http://www.mrexcel.com/forum/excel-questions/808511-looping-solver-visual-basic-applications.html

After that I've been trying this and that, getting lost again, and every-which-way. Is there a better approach you would do?

 

[hiker95]

cmondeau,

Thanks for the Private Message.

Your above link is to a word document?????

 

[cmondeau]

Yessir, it's a report with the criteria I established to complete the code. My code, is just all over the place.

 

[hiker95]

cmondeau,

In your reply #1, in the display wrapped in CODE tags, is that a sample of your raw data?

We can not tell what worksheet, cells, rows, and, columns the data is in?????


1. What version of Excel and Windows are you using?

2. Are you using a PC or a Mac?


Can you post a screenshot of the actual raw data worksheet?

And, can you post a screenshot of the worksheet results (manually formatted by you) that you are looking for?

To post a small screen shot try one of the following:

Excel Jeanie
Download

MrExcel HTML Maker
https://onedrive.live.com/?cid=8cffdec0ce27e813&sc=documents&id=8CFFDEC0CE27E813!189

Borders-Copy-Paste
http://www.mrexcel.com/forum/about-board/444901-how-create-table-like-aladin.html#post2198045

To test the above:
Test Here


Or, you can upload your workbook to Box Net,
sensitive data changed
mark the workbook for sharing
and provide us with a link to your workbook.


And, can we see your macro code?

 

[cmondeau]

To get things started...

Excel 2013
Windows

Testing Data: https://app.box.com/s/21c2dvu3degt04wlfh9q

As far as the results go, this is where I get hung up on. I've tried a few variants, but am not really satisfied because they always seem to change how I want to make the code. Any suggestions?

I really like the idea of just assigning a Stock ID to each Item No, on the condition that the Stock ID is the same until the maximum length is reached, then the Stock ID increments and the length is reset.
https://app.box.com/s/55zyydfgozjez20ca9w4

The "Location from End" could be a feature that tells you where the Item No is on that particular piece of stock.

And the coding thus far (from the colors and animals test thread, plus a few extra http://www.mrexcel.com/forum/excel-...generate-table-visual-basic-applications.html):

Sub gen_Table()

Dim MyCell As Range
Dim MyRange As Range


With Sheets("Sheet1") 'clears the data matrix'
    .Range("F1", .Cells(.Rows.Count, .Columns.Count)).Clear
End With


For Each ws In Sheets 'deletes the sheets created by the previous run'
Application.DisplayAlerts = False
If ws.Name <> "Sheet1" Then ws.Delete
Next
Application.DisplayAlerts = True


Const OutputCell As String = "E1" ' set output cell


With Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    .Offset(, 1).AdvancedFilter xlFilterCopy, , Range(OutputCell), True ' copies uniques animals in outputcell
    .AdvancedFilter xlFilterCopy, , Range(OutputCell).Offset(, 1), True ' copies unique colors on the right of outputcell
End With




With Range(OutputCell)
    .Clear ' clear outputcell header
    Range(.Offset(1, 1), Cells(.Offset(Rows.Count - .Row, 1).End(xlUp).Row, .Offset(, 1).Column)).Copy ' copy paste transpose the colors next to output cell
    .Offset(, 1).PasteSpecial , , , True
    Range(.Offset(1, 1), Cells(.Offset(Rows.Count - .Row, 1).End(xlUp).Row, .Offset(, 1).Column)).Clear
End With




With Range(OutputCell).CurrentRegion ' apply formulas to count given the 2 conditions
    With .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
        .Formula = Replace("=COUNTIFS($A$2:$A$@," & _
                    Range(OutputCell).Offset(, 1).Address(True, False) & ", $B$2:$B$@," & _
                    Range(OutputCell).Offset(1).Address(False, True) & ")", "@", Range("A" & Rows.Count).End(xlUp).Row)
        .Value = .Value
    End With
End With




Set MyRange = Sheets("Sheet1").Range("G1")
Set MyRange = Range(MyRange, MyRange.End(xlToRight))


For Each MyCell In MyRange
    Sheets.Add After:=Sheets(Sheets.Count) 'creates a new worksheet
    Sheets(Sheets.Count).Name = MyCell.Value ' renames the new worksheet
    Sheets(Sheets.Count).Range("A1") = MyCell.Value 'fills the target cell with the sheet name'
    
Next MyCell


End Sub

 

[cmondeau]

And if there is a more intuitive way that anyone else has thought of doing, please share! I'm not attached to anything

 

[cmondeau]

I've updated the code to adjust to the current data file. However, it seems to be overwriting data starting at B2.

Sub gen_Table()

With Sheets("Sheet1") 'clears the data matrix'
    .Range("F1", .Cells(.Rows.Count, .Columns.Count)).Clear
End With


For Each ws In Sheets 'deletes the sheets created by the previous run'
Application.DisplayAlerts = False
If ws.Name <> "Sheet1" Then ws.Delete
Next
Application.DisplayAlerts = True


Const OutputCell As String = "F1" ' set output cell


With Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    .Offset(, 1).AdvancedFilter xlFilterCopy, , Range(OutputCell), True ' copies unique pipe size in outputcell
    .AdvancedFilter xlFilterCopy, , Range(OutputCell).Offset(, 1), True ' copies unique length on the right of outputcell
End With




With Range(OutputCell)
    .Clear ' clear outputcell header
    Range(.Offset(1, 1), Cells(.Offset(Rows.Count - .Row, 1).End(xlUp).Row, .Offset(, 1).Column)).Copy ' copy paste transpose the pipe sizes next to output cell
    .Offset(, 1).PasteSpecial , , , True
    Range(.Offset(1, 1), Cells(.Offset(Rows.Count - .Row, 1).End(xlUp).Row, .Offset(, 1).Column)).Clear
End With




With Range(OutputCell).CurrentRegion ' apply formulas to count given the 2 conditions
    With .Resize(.Rows.Count - 1, .Columns.Count - 1).Offset(1, 1)
        .Formula = Replace("=COUNTIFS($B$2:$B$@," & _
                    Range(OutputCell).Offset(, 1).Address(True, False) & ", $C$2:$C$@," & _
                    Range(OutputCell).Offset(1).Address(False, True) & ")", "@", Range("B" & Rows.Count).End(xlUp).Row)
        .Value = .Value
    End With
End With


End Sub

 

[hiker95]

cmondeau,

If we start off with this:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Item No	Pipe Size	Length	Description	Type
2	1284	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
3	1284	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
4	1252	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
5	1162	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
6	1248	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
7	1248	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
8	1278	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
9	1278	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
10	1429	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
11	Thread-O-Let2	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
12	1426	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
13	1276	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
14	1276	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
15	1260	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
16	1260	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
17	1290	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
18	1290	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
19	1269	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
20	1269	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
21	1261	1"	54 7/8"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
22	1261	1"	54 7/8"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
23	1258	1"	64 1/4"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
24	1258	1"	64 3/8"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights
Sheet1

We end up with this:

Excel 2007
	A	B	C	D	E	F	G	H	I
1	Item No	Pipe Size	Length	Description	Type		Stock ID	Location from End
2	1284	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
3	1284	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
4	1252	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
5	1162	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
6	1248	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
7	1248	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
8	1278	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
9	1278	0 1/2"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
10	1429	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
11	Thread-O-Let2	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
12	1426	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
13	1276	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
14	1276	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
15	1260	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
16	1260	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
17	1290	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
18	1290	0 3/4"	2"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
19	1269	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
20	1269	0 3/4"	3"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
21	1261	1"	54 7/8"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
22	1261	1"	54 7/8"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
23	1258	1"	64 1/4"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		1
24	1258	1"	64 3/8"	A53 ERW Gr-B Std Wt Blk Stl Pipe PE	Straights		2
Sheet1

In the above screenshot, what goes in to column H, from the next screenshot (please manually complete this section, and, repost the workbook:

Excel 2007
	A	B	C	D	E	F
1		0 1/2"	0 3/4"	1"	1 1/2"
2	2"	8	6	0	0
3	3"	0	5	0	2
4	54 7/8"	0	0	2	0
5	64 1/4"	0	0	1	0
6	7"	0	0	1	0
7	27 1/8"	0	0	1	0
8	12"	0	0	2	0
9	242"	0	0	1	0
10	14 1/2"	0	0	0	1
11	5"	0	0	2	2
12
13
Data Matrix

 

[cmondeau]

Here's the updates Document: https://app.box.com/s/hwgjgyzmjs48bc8fgitb

Please note, the macro that exists in there now generates the Data Matrix on Sheet1. I was fiddling around with the code a bit and got some progress.
Also, Column F is just the decimal convert of the Lengths in Column C. I did this so I could quickly fill in the cells in Column H (Location from end).
Lastly, I adjusted the styling for the Stock ID. The previous example I gave was incorrect, as it assumed all pipe was the same thickness. More on this below...

The Location from the End is entirely dependent on the Stock ID. This is because the Stock ID is created by using the optimized template from the Solver Add-In. It's a little difficult to see on Stocks A through B, but Stock C is where things get interesting.
The link to the new file is my quick and dirty optimizing method, very similar to the Greedy Algorithm (Greedy algorithm - Wikipedia, the free encyclopedia). So this doesn't exactly represent the naming. I plan on maximizing the amount of stock pipe used by filling in as many Items(parts) that will fit. The constraint here is the total length cannot exceed 240" (but would like to keep this length a user defined variable). back to the Location from the End. Basically this is just the measurement from the origin of the pipe. Say you have three parts: 12", 8", and 6" in that order. The corresponding locations would be 0", then the second part would be placed right after. This would then make the third part to be placed at 20" from the origin. The reasoning for having this is to in the future, add a saw kerf variable which accounts for waste material from cutting, and possibly implementing a visual template of how the parts are laid out.

Forgive me for the incomplete table, leaving the office and only computer at the moment. Can view from the phone

Almost forgot the stock naming convention. The different letters represent the different size of pipe, and the numbers represent the different item per stock. I think this could be simplified by creating new sheets for each size pipe (diameter) and placing the numeric stock ID's in their appropriate sheet.