Excel query

  yeldud 15:41 09 Mar 2005
Locked

I use Solidworks 2005 to design machines and can create a Bill of Materials (BOM) that can be exported as either an Excel or csv file. Some of the machines are quite large therefore creating one BOM for the whole machine is not practical, I break each machine down into various conveniently sized sub-assemblies and create individual BOMs for each sub-assembly. This is fine until I need to create a requisition for standard bought out parts such as nuts, bolts and washers etc. The same part maybe called up in various BOMS, what I would like to do is amalgamate the BOMs so that if the same part is called up in different BOMS it can be called up in a requisition as one item but with the quantity amended to reflect the total number required . The description of each item is spread over three main columns i.e. DESCRIPTION (i.e. NUT), SIZE (i.e. M6) and QUANTITY (i.e. 3). The exported BOM would also have other columns such as ‘Item No’, ‘Supplier’ and ‘Drawing No’ that I want to ignore.

As a complete novice in the use of Excel I have no idea if this can be done but if it can and some one can point me in the right direction it would make my life a lot easier.

  VoG II 15:56 09 Mar 2005

I'm not entirely clear what you want to do but ASAPUtilities click here may help you.

In ASAP, Sheets -> Summarize rows or columns from several sheets.

This utility will combine the selected columns of each sheet into a new sheet (summary).

Usage:

Select the desired sheets.

Start this utility, then select the column(s) you wish to summarise.

  compumac 17:33 09 Mar 2005

I used to do this when I was a buyer, but the main thing was to give each individaul component a part number so that one does not have to check each column for size/diameter/length etc. I had to do this and to include adhesives which one purchased in gallon containers but only used a small percentage of that container for one item of machinery. It is long winded in the first instance but so much easier for identification purposes. it also ensures that if others were to add to your BOM's you would be all inserting the same spec as opposed to your individual interpretation of the same thing.
I will look up as to how I used to do this and will come back on it.

  yeldud 19:14 09 Mar 2005

Downloaded ASAP Utilities as you advised but all I seem to be able to do is the equivalent of doing a cut and paste of one BOM into sheet 2 of another BOM.
What I need to do is if all the contents of one row are the same as the contents of another row with the exception of the quantity then I want to add the quantities together to give me a total call off for that particular item. Hope that clarifies things and thanks for the help so far.

  VoG II 19:16 09 Mar 2005

Ah, the BOMs are each in separate workbooks?

  yeldud 19:21 09 Mar 2005

Correct!!

  VoG II 19:37 09 Mar 2005

Does click here look like the sort of thing you are after?

I can help you with how to load this macro and modify it.

  yeldud 19:49 09 Mar 2005

Does look a bit like a solution, but I have no idea if it will give me the total quantities I'm after. I have 2 sample BOMs that I could e-mail to you to illustrate more clearly what I am trying to achieve.

  VoG II 20:20 09 Mar 2005

You should have mail - if not please click my yellow envelope.

  VoG II 19:23 12 Mar 2005

The BOM files contained the following in columns A to G

ITEM NO. DRG No DESCRIPTION MATERIAL PART No/FINISHED SIZE SUPPLIER QTY.

These "headings" were contained in the LAST row of each file.

A file "master.xls" is placed in the same folder as (only) the BOM files to be operated on. Then the following code is run. I am told that it "works a treat":

Dim ToBook As String, ToSheet As Worksheet, ToRow As Long, FromBook As String

Dim FromSheet As Worksheet, FromRow As Long, LastRow As Long

Sub CONSOL()

Application.Calculation = xlCalculationManual

ChDrive ActiveWorkbook.Path

ChDir ActiveWorkbook.Path

ToBook = ActiveWorkbook.Name

Sheets.Add

Set ToSheet = ActiveSheet

Application.ScreenUpdating = False

ToRow = 2

FromBook = Dir("*.xls")

While FromBook <> ""

If FromBook <> ToBook Then

Application.StatusBar = FromBook

Call Transfer_data

End If

FromBook = Dir

Wend

Application.StatusBar = False

Application.Calculation = xlCalculationAutomatic

Range("A1").Value = "DESCRIPTION"

Range("B1").Value = "QUANTITY"

Range("C1").Value = "DRG No"

Range("A1:C" & ToRow).Select

Selection.Sort Key1:=Range("A2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(2), _
Replace:=True, PageBreaks:=False, SummaryBelowData:=True

ActiveSheet.Outline.ShowLevels RowLevels:=2

Columns("A:C").EntireColumn.AutoFit

Range("A1").Select

Application.ScreenUpdating = True

End Sub

Private Sub Transfer_data()

Dim iRow As Integer, Desc As String

Workbooks.Open Filename:=FromBook

For Each FromSheet In Workbooks(FromBook).Worksheets

LastRow = FromSheet.Range("A65536").End(xlUp).Row - 1

For iRow = 1 To LastRow

Desc = FromSheet.Cells(iRow, 3).Value & " " & FromSheet.Cells(iRow, 5).Value

ToSheet.Cells(ToRow, 1).Value = Desc

ToSheet.Cells(ToRow, 2).Value = FromSheet.Cells(iRow, 7).Value

ToSheet.Cells(ToRow, 3).Value = FromSheet.Cells(iRow, 2).Value

ToRow = ToRow + 1

Next iRow

ToRow = ToSheet.Range("A65536").End(xlUp).Row + 1

Next

Workbooks(FromBook).Close savechanges:=False

End Sub

Noted

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

AMD Radeon Adrenalin release date, new features, compatible graphics cards

8 brilliant character artists speaking at Pictoplasma 2018

iMac Pro release date, UK price & specs

Football : comment regarder la Ligue 1 en direct ?