Bolt Counts

[Engineeeeeeeeer]

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 (A2-70, A4-70, 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]

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]

This seems like something you should be doing in a database... You could export it from excel.

 

[Denial]

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 user-defined 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.

Option 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 two-dimensional array.
'    SortArray  -  The two-dimensional 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