## Bolt Counts

## Bolt Counts

(OP)

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,

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,

## RE: Bolt Counts

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

## RE: Bolt Counts

## RE: Bolt Counts

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.

## CODE

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