Aggregate Bill Of Material

[Suhtka]

Hi, I have a program that generates a cut list of materials depending on size and type of unit. Now the guys just take the files and manually aggregate it. I would like to do this programatically. I want to sum the quantity where the width, length, thick, and Matl. match. Red totals 20 parts. I would send the file but it seems I can't attach one.

Thank You!

ITEM	QTY	WIDTH	LENGTH	THICK	MATL
1	4	23	42	13/16	Solid Ash
2	2	26 3/8	32 7/8	3/4	Oak Ply
3	2	16 3/4	32 7/8	3/4	Oak Ply
4	2	3	32 3/8	3/4	Solid Ash
5	2	2 3/4	32 3/8	3/4	Solid Ash
6	2	1 1/2	32 3/8	3/4	Birch Ply
7	4	1 3/4	32 3/8	3/4	Birch Ply
8	4	2	9 1/2	1 1/4	Hardwood
9	4	1 1/2	19 1/2	3/4	Birch Ply
10	2	14 1/2	29 3/8	1/2	CDX Plywood
1	4	23	42	13/16	Solid Ash
2	2	26 3/8	46 7/8	3/4	Oak Ply
3	2	16 3/4	46 7/8	3/4	Oak Ply
4	2	3	46 3/8	3/4	Solid Ash
5	2	2 3/4	46 3/8	3/4	Solid Ash
6	2	1 1/2	46 3/8	3/4	Birch Ply
7	4	1 3/4	46 3/8	3/4	Birch Ply
8	4	2	9 1/2	1 1/4	Hardwood
9	4	1 1/2	19 1/2	3/4	Birch Ply
10	2	14 1/2	43 3/8	1/2	CDX Plywood
1	8	23	42	13/16	Solid Ash
2	4	26 3/8	52 7/8	3/4	Oak Ply
3	4	16 3/4	52 7/8	3/4	Oak Ply
4	4	3	52 3/8	3/4	Solid Ash
5	4	2 3/4	52 3/8	3/4	Solid Ash
6	4	1 1/2	52 3/8	3/4	Birch Ply
7	8	1 3/4	52 3/8	3/4	Birch Ply
8	8	2	9 1/2	1 1/4	Hardwood
9	8	1 1/2	19 1/2	3/4	Birch Ply
10	4	14 1/2	49 3/8	1/2	CDX Plywood
1	8	23	42	13/16	Solid Ash
2	4	26 3/8	64 7/8	3/4	Oak Ply
3	4	16 3/4	64 7/8	3/4	Oak Ply
4	4	3	64 3/8	3/4	Solid Ash
5	4	2 3/4	64 3/8	3/4	Solid Ash
6	4	1 1/2	64 3/8	3/4	Birch Ply
7	8	1 3/4	64 3/8	3/4	Birch Ply
8	8	2	9 1/2	1 1/4	Hardwood
9	8	1 1/2	19 1/2	3/4	Birch Ply
10	4	14 1/2	61 3/8	1/2	CDX Plywood

<tbody>
</tbody>

 

[Snackan1986]

Hi,

Tried if pivot table gives you what you want?

 

[Suhtka]

Hi,

Tried if pivot table gives you what you want?

I cant use a pivot table all the other sections have been automated

 

[MickG]

Try this for results on sheet2.

Sub MG03Nov36
Dim Rng As Range, Dn As Range, n As Long, txt As String, Ac As Long
Set Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
ReDim ray(1 To Rng.Count, 1 To 6)
With CreateObject("scripting.dictionary")
.CompareMode = vbTextCompare
For Each Dn In Rng
    txt = Dn.Offset(, 2).Value & "," & Dn.Offset(, 3).Value & "," & _
        Dn.Offset(, 4).Value & Dn.Offset(, 5).Value
    If Not .Exists(txt) Then
        n = n + 1
        For Ac = 1 To 6
            ray(n, Ac) = Format(Dn.Offset(, Ac - 1), "@")
        Next Ac
        .Add txt, n
    Else
        ray(.Item(txt), 2) = ray(.Item(txt), 2) + Dn.Offset(, 1)
    End If
Next
End With
With Sheets("Sheet2").Range("A1").Resize(n, 6)
.Value = ray
 .Borders.Weight = 2
  .Columns.AutoFit
End With
End Sub

Regards Mick

 

[MARZIOTULLIO]

=sumif(a2:a41,a2,b2:b41)

 

[MARZIOTULLIO]

wrong formula

 

[sekar]

Hi, I have a program that generates a cut list of materials depending on size and type of unit. Now the guys just take the files and manually aggregate it. I would like to do this programatically. I want to sum the quantity where the width, length, thick, and Matl. match. Red totals 20 parts. I would send the file but it seems I can't attach one.

Thank You!

ITEM	QTY	WIDTH	LENGTH	THICK	MATL
1	4	23	42	13/16	Solid Ash
2	2	26 3/8	32 7/8	3/4	Oak Ply
3	2	16 3/4	32 7/8	3/4	Oak Ply
4	2	3	32 3/8	3/4	Solid Ash
5	2	2 3/4	32 3/8	3/4	Solid Ash
6	2	1 1/2	32 3/8	3/4	Birch Ply
7	4	1 3/4	32 3/8	3/4	Birch Ply
8	4	2	9 1/2	1 1/4	Hardwood
9	4	1 1/2	19 1/2	3/4	Birch Ply
10	2	14 1/2	29 3/8	1/2	CDX Plywood
1	4	23	42	13/16	Solid Ash
2	2	26 3/8	46 7/8	3/4	Oak Ply
3	2	16 3/4	46 7/8	3/4	Oak Ply
4	2	3	46 3/8	3/4	Solid Ash
5	2	2 3/4	46 3/8	3/4	Solid Ash
6	2	1 1/2	46 3/8	3/4	Birch Ply
7	4	1 3/4	46 3/8	3/4	Birch Ply
8	4	2	9 1/2	1 1/4	Hardwood
9	4	1 1/2	19 1/2	3/4	Birch Ply
10	2	14 1/2	43 3/8	1/2	CDX Plywood
1	8	23	42	13/16	Solid Ash
2	4	26 3/8	52 7/8	3/4	Oak Ply
3	4	16 3/4	52 7/8	3/4	Oak Ply
4	4	3	52 3/8	3/4	Solid Ash
5	4	2 3/4	52 3/8	3/4	Solid Ash
6	4	1 1/2	52 3/8	3/4	Birch Ply
7	8	1 3/4	52 3/8	3/4	Birch Ply
8	8	2	9 1/2	1 1/4	Hardwood
9	8	1 1/2	19 1/2	3/4	Birch Ply
10	4	14 1/2	49 3/8	1/2	CDX Plywood
1	8	23	42	13/16	Solid Ash
2	4	26 3/8	64 7/8	3/4	Oak Ply
3	4	16 3/4	64 7/8	3/4	Oak Ply
4	4	3	64 3/8	3/4	Solid Ash
5	4	2 3/4	64 3/8	3/4	Solid Ash
6	4	1 1/2	64 3/8	3/4	Birch Ply
7	8	1 3/4	64 3/8	3/4	Birch Ply
8	8	2	9 1/2	1 1/4	Hardwood
9	8	1 1/2	19 1/2	3/4	Birch Ply
10	4	14 1/2	61 3/8	1/2	CDX Plywood

<tbody>
</tbody>

hi could repost or msg the working code please. i am getting error, subscript out of range

 

[Fluff]

If you are referring to the code in post#4 try copying it again, I have removed the bb code.

 

[sekar]

Thanks Fluff, i tried, it worked. But not as desired. The output throws nearly 30 rows in sheet2, instead of summing up the qty of unique materials. Could you please check on this.

 

[Fluff]

In that case I suggest that you start a new thread, providing some more information, along with some data.