INTELLIGENT WORK FORUMS FOR ENGINEERING PROFESSIONALS
Come Join Us!
Are you an Engineering professional? Join EngTips now!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*EngTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.

As part of my job I have to specify and order the nuts/bolts/washers for our project equipment. I have been using an excel spreadsheet to list all the bolts (during the design we try to minimise different types and lengths of bolts where possible). The spreadsheet has the item description (ie filter housing), the bolt size (M10, M12 etc), the bolt length (mm), the bolt type (hex head, cap screw etc), the material (A270, A470, Gr8.8 etc), the type of washers required and the quantity.
The drawings are examined and the fastener data for each item/component is then added to the excel table  that is the relatively easy, although tedious bit.
The problem I have is then going through the table to pick out all the same size bolts/washers/nuts etc of the same meterials to list for when we order the fasteners from one of our suppliers. Mistakes are being made at this stage that possibly means a shortage of bolts on the work site.
My questions are: 1. Does anyone use such an excel sheet successfully for a similar type of thing? (any samples/links??) 2. Is there software available for this sort of thing?
Any comments would be most welcome as I am coming close to counting my last bolts.....
Thanks, 

yogia (Structural) 
22 Jun 07 19:47 
Hi Engineeeeeeeeer: Not knowing exactly how your data is laid out, it is hard to talk of actual formulations, but how about looking into using one or more of ... 1) COUNTIF 2) SUMIF 3) SUMPRODUCT 4) DSUM, DCOUNT, ... 4) AdvancedFilter 5) PivotTable 6) DataTable 7) .... functions/features in EXCEL. Yogi Anand, D.Eng, P.E. Energy Efficient Building Network LLC ANAND Enterprises LLC http://www.energyefficientbuild.com 

RossABQ (Mechanical) 
23 Jun 07 0:43 
This seems like something you should be doing in a database... You could export it from excel. 

Denial (Structural) 
24 Jun 07 18:37 
I am not totally clear on what it is you are trying to do, but it seems to have some similarities to something I had to do once. Here are the starting steps for what I believe could be one approach. Your "item descriptions" and counts are in what I will call your primary range. Create a secondary range that contains each item description only once. Then use the COUNTIF and SUMIF functions, summing the appropriate entries in your primary range but getting your "criteria" from the secondary range. For my problem, I wrote a userdefined function to create the secondary range from the primary range, and (FWIW) I reproduce the VBA code below. Note that the function is an "array function". Hope this heeeeeeeeelps. CODEOption Explicit Option Base 1 ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Public Function Remove_Dups(In_List As Range) ' ' Takes as input a column of values. ' Creates from these values a sorted list without duplicates, which ' is returned to the calling spreadsheet as an array variable. ' Dim InRows As Long, InCols As Long, OutRows As Long, OutCols As Long Dim I As Long, J As Long, NumEntries As Long Dim ErrText As String Dim Ans() As Variant, SortedList() As Variant Const FnName As String = "Function Remove_Dups" Const EmptyMark As String = "" ' ' Get the sizes of the input range and the output range. ' InRows = In_List.Rows.Count InCols = In_List.Columns.Count OutRows = Application.Caller.Rows.Count OutCols = Application.Caller.Columns.Count ' ' We now know the required sizes for several VBA arrays, so ' declare them accordingly. ' ReDim Ans(OutRows, OutCols) ReDim SortedList(OutRows, 1) ' ' Apply a few checks to these array sizes before going any further. ' The "OutRows<InRows" test can be left out if you are feeling brave. ' If InCols <> 1 Or OutCols <> 1 Or InRows < 2 Or OutRows < InRows Then ErrText = "Problem with sizes of input or output ranges." GoTo ErrorReturn End If ' ' Create a VBA array containing the entries to be processed. ' Skip over empty cells, and also skip cells containing ' the "EmptyMark". ' ' (The EmptyMark bit can be changed or dropped as required.) ' NumEntries = 0 For I = 1 To InRows If Not IsEmpty(In_List(I, 1)) And In_List(I, 1) <> EmptyMark Then NumEntries = NumEntries + 1 SortedList(NumEntries, 1) = In_List(I, 1) End If Next I ' ' If the input range contains no valid entries, go gentle into the night. ' If NumEntries < 1 Then For I = 1 To OutRows Ans(I, 1) = EmptyMark ' Could use "" here instead. Next I Remove_Dups = Ans Exit Function End If ' ' Sort the array. ' Call QuickSort(SortedList, 1, 1, NumEntries) ' ' Scan through the sorted array, grabbing the first instance of ' each unique entry as we go, and putting it into the output array. ' J = 1 Ans(1, 1) = SortedList(1, 1) For I = 2 To NumEntries If SortedList(I, 1) <> SortedList(I  1, 1) Then J = J + 1 Ans(J, 1) = SortedList(I, 1) End If Next I ' ' Fill the remainder of the output array with "Emptymark". ' If J < OutRows Then For I = J + 1 To OutRows Ans(I, 1) = EmptyMark Next I End If ' ' It's all over, Red Rover. ' Remove_Dups = Ans Exit Function ' ' Error handling area. ' ErrorReturn: For I = 1 To OutRows Ans(I, 1) = CVErr(xlErrNA) ' Fill output cells with "#N/A" Next I MsgBox ErrText, , FnName Remove_Dups = Ans End Function ' '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' Private Sub QuickSort(SortArray, col, L, R) ' ' Performs a "quicksort" on a twodimensional array. ' SortArray  The twodimensional array to be sorted. ' col  The (single) column number containing the sort key. ' L  The first row number of the band to be sorted. ' R  The last row number of the band to be sorted. ' ' Always sorts in ASCENDING order. ' ' Filched off Google Groups in June 2004. ' ' Originally Posted by Jim Rech 10/20/98 Excel.Programming ' Modified to sort on first column of a two dimensional array. ' Modified to handle a sort column other than 1 (or zero). ' Dim I As Long, J As Long, mm As Long Dim x As Variant, y As Variant ' ' Set new extremes to old extremes. ' Get sort key for row in middle of new extremes. ' I = L J = R x = SortArray((L + R) / 2, col) ' ' Loop for all rows between the extremes. ' While (I <= J) ' ' Find the first row whose key is greater than that of the middle row. ' While (SortArray(I, col) < x And I < R) I = I + 1 Wend ' ' Find the last row whose key is less than that of the middle row. ' While (x < SortArray(J, col) And J > L) J = J  1 Wend ' ' If the new "greater" row is smaller than the new "lesser" row ' swap them, then advance the pointers to the next rows. ' If (I <= J) Then For mm = LBound(SortArray, 2) To UBound(SortArray, 2) y = SortArray(I, mm) SortArray(I, mm) = SortArray(J, mm) SortArray(J, mm) = y Next mm I = I + 1 J = J  1 End If Wend ' ' Recurse to sort the lower then the upper halves of the extremes. ' If (L < J) Then Call QuickSort(SortArray, col, L, J) If (I < R) Then Call QuickSort(SortArray, col, I, R) ' End Sub




