Summarising Multiple BOM Tables into one - best solution?

[jozzy]

G'day folks,

So I am looking for multiple/best solution(s) to summarise/consolidate multiple tables into one. These tables are Bill of Materials (BOM's), on each worksheet will be a table summarising the materials required for each product assembly. The tables are made up of Part Numbers, Cutting Length and Quantity the end result should reflect the results as per my example below (Summary - worksheet 4).

Ideally the solution should be dynamic - if one table is updated the summary would reflect this update.

I have tried going down the Pivot table route and this to me is the most logical solution but I seem to be running into problems with the Pivot Table summarising my lengths, this is not what I require as each Part must be cut to the specified length. (This may be the result of me not driving a Pivot Table properly )

BOM1 (Worksheet 1)

Part	Length	Quantity
P101	1800	2
P101	1500	1
P110	600	6
P120	600	6

<tbody>
</tbody>

BOM2 (Worksheet 2)

Part	Length	Quantity
P101	1800	5
P110	600	10
P120	600	10

<tbody>
</tbody>

BOM3 (Worksheet 3)

Part	Length	Quantity
P101	1800	1
P101	1900	1
P110	600	4
P120	600	4

<tbody>
</tbody>

Summmary (Worksheet 4) - EXAMPLE

Part	Length	Quantity
P101	1900	1
P101	1800	8
P101	1500	1
P110	600	20
P120	600	20

<tbody>
</tbody>

Should you require more info please feel free to ask.


Cheers,
Sean

Excel 2010

 

[jozzy]

The summary sheet should collate all the data from each assembly and group similar item Descriptions, Dimensions and Quantities together to produce a summary sheet.


Each assembly sheet will contain the following cells.
A12:A33 Item Description.
B12:B33 Height.
C12:C33 Length.
D12:D33 Quantity.


A user may be required to add or remove additional Assembly sheets at a later stage, so the ability to recalculate the summary is required.

Link to sample workbook: https://onedrive.live.com/redir?resid=79626E1654A1048F!113

 

[hiker95]

jozzy,

Your latest link will not work for me.


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.


If you are not able to provide your workbook on Box, then:

Click on the Reply to Thread button, and just put the word BUMP in the thread. Then, click on the Post Quick Reply button, and someone else will assist you.

 

[jozzy]

jozzy,

Your latest link will not work for me.


You can upload your workbook to Box Net,

Right! Signed up to Box so hopefully this should work without any issues. See link below....

https://app.box.com/s/6x3ju47a7bf5u3nv0qz5



Cheers,

Sean

 

[hiker95]

jozzy,

Thanks for the workbook.

Having the actual raw data worksheets does make a big difference.

I will be back later today with a new macro solution.

 

[jozzy]

Hey Hiker95

Any luck with this one or am I trying to achieve the impossible?


Cheers,

Sean

 

[hiker95]

jozzy,

Sorry about that.

Sample raw data worksheets:

Excel 2007
	A	B	C	D
11	Description	Height	Length	Quantity
12	GL_001	1197	1742	2
13	COR_01	1197	1742	1
14	AL_100	0	1705	1
15	AL_100	0	1160	2
16	AL_200	0	1705	1
17	AL_300	0	1705	1
18	AL_400	0	1705	1
19	AL_300	0	1160	2
20	0	0	0	0
21	0	0	0	0
22	0	0	0	0
23	0	0	0	0
24	CAST_001	0	0	4
25	CAST_002	0	0	2
26	0	0	0	0
27	0	0	0	0
28	0	0	0	0
29	0	0	0	0
30	0	0	0	0
31	0	0	0	0
32	0	0	0	0
33	0	0	0	0
34
Assembly (1)

Excel 2007
	A	B	C	D
11	Description	Height	Length	Quantity
12	GL_001	1197	1742	2
13	COR_01	1197	1742	1
14	AL_100	0	1960	1
15	AL_100	0	1160	2
16	AL_200	0	1960	1
17	AL_300	0	1960	1
18	AL_400	0	1960	1
19	AL_300	0	1160	2
20	0	0	0	0
21	0	0	0	0
22	0	0	0	0
23	0	0	0	0
24	CAST_001	0	0	4
25	CAST_002	0	0	2
26	0	0	0	0
27	0	0	0	0
28	0	0	0	0
29	0	0	0	0
30	0	0	0	0
31	0	0	0	0
32	0	0	0	0
33	0	0	0	0
34
Assembly (2)

Excel 2007
	A	B	C	D
11	Description	Height	Length	Quantity
12	GL_001	1197	1742	2
13	COR_01	1197	1742	1
14	AL_100	0	1960	5
15	AL_100	0	1160	10
16	AL_200	0	1960	5
17	AL_300	0	1960	5
18	AL_400	0	1960	5
19	AL_300	0	1160	10
20	0	0	0	0
21	0	0	0	0
22	0	0	0	0
23	0	0	0	0
24	CAST_001	0	0	4
25	CAST_002	0	0	2
26	0	0	0	0
27	0	0	0	0
28	0	0	0	0
29	0	0	0	0
30	0	0	0	0
31	0	0	0	0
32	0	0	0	0
33	0	0	0	0
34
Assembly (3)

after the macro in worksheet Summary:

Excel 2007
	A	B	C	D
1	Description	Height	Length	QUANTITY
2	AL_100	0	1160	14
3	AL_100	0	1705	1
4	AL_100	0	1960	6
5	AL_200	0	1705	1
6	AL_200	0	1960	6
7	AL_300	0	1160	14
8	AL_300	0	1705	1
9	AL_300	0	1960	6
10	AL_400	0	1705	1
11	AL_400	0	1960	6
12	CAST_001	0	0	12
13	CAST_002	0	0	6
14	COR_01	1197	1742	3
15
Summary

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

1. Copy the below code
2. Open your NEW workbook
3. Press the keys ALT + F11 to open the Visual Basic Editor
4. Press the keys ALT + I to activate the Insert menu
5. Press M to insert a Standard Module
6. Where the cursor is flashing, paste the code
7. Press the keys ALT + Q to exit the Editor, and return to Excel
8. To run the macro from Excel press ALT + F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sub SummariseAssemblys()
' hiker95, 10/16/2014, ME801072
Dim wa As Worksheet, ws As Worksheet
Dim r As Long, lr As Long, n As Long, nr As Long
Application.ScreenUpdating = False
If Not Evaluate("ISREF(Summary!A1)") Then Worksheets.Add().Name = "Summary"
Set ws = Sheets("Summary")
With ws
  .UsedRange.Clear
  With .Cells(1, 1).Resize(, 4)
    .Value = Array("Description", "Height", "Length", "QUANTITY")
    .Font.Bold = True
  End With
End With
For Each wa In ThisWorkbook.Worksheets
  If wa.Name <> "Summary" Then
    With wa
      lr = .Cells(Rows.Count, 1).End(xlUp).Row
      nr = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row + 1
      .Range("A12:D" & lr).Copy Destination:=ws.Range("A" & nr)
      Application.CutCopyMode = False
    End With
  End If
Next wa
With ws
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:D" & lr).Sort key1:=Range("A2"), order1:=1, key2:=Range("B2"), order2:=2
  With .Range("E2:E" & lr)
    .FormulaR1C1 = "=RC[-4]&RC[-3]&RC[-2]"
    .Value = .Value
  End With
  For r = lr To 2 Step -1
    If .Cells(r, 1) = 0 Or InStr(.Cells(r, 1), "GL_") Then .Rows(r).Delete
  Next r
  lr = .Cells(Rows.Count, 1).End(xlUp).Row
  .Range("A2:E" & lr).Sort key1:=.Range("E2"), order1:=1
  For r = 2 To lr
    n = Application.CountIf(.Columns(5), .Cells(r, 5).Value)
    If n > 1 Then
      .Range("D" & r).Value = Evaluate("=Sum(D" & r & ":D" & r + n - 1 & ")")
      .Range("A" & r + 1 & ":D" & r + 1 + n - 2).ClearContents
    End If
    r = r + n - 1
  Next r
  .Columns(5).ClearContents
  .Range("A2:D" & lr).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
  .Columns(1).Resize(, 4).AutoFit
  .Activate
End With
Application.ScreenUpdating = True
End Sub

Before you use the macro with Excel 2007 or newer, save your workbook, Save As, a macro enabled workbook with the file extension .xlsm

Then run the SummariseAssemblys macro.