Auto Filter and Sum Results (Material Filter and Optimser)

Hi Guys,

I have built myself a metal fab shop costing workbook but I would now like to auto filter the material list and sum the metres of each material size. Sometimes there are several parts in a job which use similar material. I would like to auto filter these into one row with the rounded total lengths required. I have attached a sample sheet to explain what I am trying to achieve. First Table is the sample raw data produced and the Second is basically what I'm trying to achieve.

List of what I'm hoping to achieve

1. Auto Filter the Product Code column for like product and transfer across all related row data but sum the total metres required

2. Work out a optimized cut list and display a total rounded lengths required. Material Type, Material Length, Part Length and Part Qty will all be pulled from a master data sheet and the Original Job Worksheet. This is just a sample of what I'm trying to achieve. Basically so as the job is quoted, the worksheet is working out a material list required.

3. If a per length cut list is a byproduct of the material optimizer, that would greatly help.

As you can see Row 15 in Test Sample doesn't work simply by dividing the total metres by the length metres. As some parts are quite long you will need several lengths in order to achieve these cut pieces with large offcut wastage. I have being searching your forum and have found a material optimizer which works really well but only upto 8 different part lengths and 5 cuts per material length. Is there a way that this can be achieved automatically as the table is updated (Necessary data is being pulled from a master data worksheet as part particulars are input). I'm not sure if the optimizer is the best way too achieve the outcome but thought I would give as much info as possible.

Thank you in advance

Hello Ross82 and Welcome to Excel Forum.
This proposal only addresses the first paragraph.
The 'first table' is converted to an actual Excel table which requires unmerging some columns in the original. Since there were two 'size (mm)' columns I deleted one, if they are both needed then both will need a distinct heading.
A pivot table references the table using 'Product code' through 'Material length' in the row fields and 'Total metres' in the value field.
The 'Theoretical' and 'Actual' total lengths are not part of the pivot table though the column headers are formatted for aesthetic purposes.
Theoretical Total Lengths is populated using: =IF(A22="","",F22/E22)
Actual Total Lengths is populated using: =IF(A22="","",ROUNDUP(G22,0))
Let us know if you have any questions.

I've looked at the Materials Optimser file and it seems to be powered by VBA, of which I know very little. If the Test Sample spreadsheet is now functioning as desired I would suggest marking this thread as 'Solved' using the thread tools menu above your first post and then opening a new thread on the Excel Programming / VBA / Macros forum.
IMO as a new thread it will get more attention and as a thread on the VBA forum, it will get looked at mainly by contributors that have expertise in coding and modifying code.
Let us know if you have any questions.

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.