Summarising Multiple BOM Tables into one - best solution?

J

jozzy

New Member
Joined
May 20, 2009
Messages
21
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).

:warning: 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 :stickouttounge:)

BOM1 (Worksheet 1)
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]Part[/TD]
[TD="width: 64"]Length[/TD]
[TD="width: 64"]Quantity[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]P110[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]P120[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]6[/TD]
[/TR]
</tbody>[/TABLE]

BOM2 (Worksheet 2)
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]Part[/TD]
[TD="width: 64"]Length[/TD]
[TD="width: 64"]Quantity[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]P110[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]P120[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]10[/TD]
[/TR]
</tbody>[/TABLE]

BOM3 (Worksheet 3)
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]Part[/TD]
[TD="width: 64"]Length[/TD]
[TD="width: 64"]Quantity[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1900[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]P110[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD]P120[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]4[/TD]
[/TR]
</tbody>[/TABLE]

Summmary (Worksheet 4) - EXAMPLE
[TABLE="class: grid, width: 192"]
<tbody>[TR]
[TD="width: 64"]Part[/TD]
[TD="width: 64"]Length[/TD]
[TD="width: 64"]Quantity[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1900[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]P101[/TD]
[TD="align: right"]1500[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]P110[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]P120[/TD]
[TD="align: right"]600[/TD]
[TD="align: right"]20[/TD]
[/TR]
</tbody>[/TABLE]


Should you require more info please feel free to ask.


Cheers,
Sean

Excel 2010
 
Sort by date Sort by votes
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
 
Last edited:
Upvote 0

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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.
 
Upvote 0
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.
 
Upvote 0
jozzy,

Sorry about that.

Sample raw data worksheets:


Excel 2007
ABCD
11DescriptionHeightLengthQuantity
12GL_001119717422
13COR_01119717421
14AL_100017051
15AL_100011602
16AL_200017051
17AL_300017051
18AL_400017051
19AL_300011602
200000
210000
220000
230000
24CAST_001004
25CAST_002002
260000
270000
280000
290000
300000
310000
320000
330000
34
Assembly (1)



Excel 2007
ABCD
11DescriptionHeightLengthQuantity
12GL_001119717422
13COR_01119717421
14AL_100019601
15AL_100011602
16AL_200019601
17AL_300019601
18AL_400019601
19AL_300011602
200000
210000
220000
230000
24CAST_001004
25CAST_002002
260000
270000
280000
290000
300000
310000
320000
330000
34
Assembly (2)



Excel 2007
ABCD
11DescriptionHeightLengthQuantity
12GL_001119717422
13COR_01119717421
14AL_100019605
15AL_1000116010
16AL_200019605
17AL_300019605
18AL_400019605
19AL_3000116010
200000
210000
220000
230000
24CAST_001004
25CAST_002002
260000
270000
280000
290000
300000
310000
320000
330000
34
Assembly (3)


after the macro in worksheet Summary:


Excel 2007
ABCD
1DescriptionHeightLengthQUANTITY
2AL_1000116014
3AL_100017051
4AL_100019606
5AL_200017051
6AL_200019606
7AL_3000116014
8AL_300017051
9AL_300019606
10AL_400017051
11AL_400019606
12CAST_0010012
13CAST_002006
14COR_01119717423
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.

Code: 
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Lookup Multiples from Exploded BOM Lines and return units used
Replies
0
Views
718
C Goody
C
M
  • Question Question
Summarising multiple tables from one sheet into one table/pivot (size of tables vary daily)
Replies
0
Views
405
mikansang
M
J
  • Question Question
Combine data range from multiple workbooks into one worksheet with additional variable
Replies
8
Views
5K
jessrabbit
J
C
  • Question Question
VBA to copy multiple pivot tables to one worksheet
Replies
5
Views
14K
D3allamerican07
D
O
  • Question Question
Dynamically populate cells 14 rows apart with data from list on other sheet
Replies
0
Views
578
ohnow
O

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top