Matching a column of shaft sizes to a column of hole sizes to find best match for all

I needs some help matching a sample of shafts to a sample of holes. I've measure all the parts radius' and put them into an excel spreadsheet. The shaft has two radiuses and the holes have two radiuses. I created three worksheets. The first worksheet has the data for the shaft's two radiuses, the second worksheet has the data for the hole's two radius, and the third work sheet is for matching. I would like to know what the best hole and shaft match (a big shaft paired with big hole and vice versa) is for the entire sample and have the serial numbers matched in the Best Fit worksheet. Also, I would like to list the parts that cannot be match (if they cannot be matched without an interference fit).

Best Fit.xlsx


Thanks,
Brett C.

Very interesting Engineering question.

I think 'Monte Carlo' simulation would work well here. There is probably an
algorithmic approach to solving your problem, but it is beyond the scope of
my expertise. I thought about sorting the data according to 'deltas', but
it seems to me that random matching would probably yield better results.

-------------------------------------
Here is the approach I would take:
1. Randomize the 'hole sets' by serial number.
2. Randomize the 'shaft sets' by serial number.
3. Process the first 'hole set' in the randomized list.
4. Find the first 'shaft set' that is an 'acceptable fit' to the first 'hole set'.
An acceptable fit is where both hole-shaft deltas are:

[FitLowTolerance = tightest fit allowed] <= x <= [FitHiTolerance = loosest fit allowed]

5. If an 'acceptable fit' is found, determine a score for the 'shaft set'
to 'hole set' combination.
6. Save the matching items in the 'Match/No Match Data structure' detailed below.
7. If there are no 'acceptable fits', mark the 'hole set' as 'no match'.
8. Repeat steps 3-7 for the remainder of the 'hole sets'.
9. When all the 'hole sets' have been evaluated, add up the score for the simulation.
The results of the first simulation become the 'Strawman', and are saved in the
'Strawman' data structure, which is the same as the 'Match/No Match Data Structure'.
10. Repeat steps 1 thru 9 for the number of simulations you want to do.
If the score for a simulation is BETTER (LOWER) than the 'Strawman', that simulation
becomes the new 'Strawman'.

NOTE: If the are a different number of 'hole sets' and 'shaft sets' the
one with the smaller number should be in the outer loop. For example,
if there were 4 'hole sets' and 5 'shaft sets' use the algorithm as written
above. If the numbers were reversed, step 1 would be 'shaft sets'.

-------------------------------------
Match/No Match Data Structure:
1. Column A - Shaft Serial Number that matches Hole Serial Number
2. Column B - Hole Serial Number that matches Shaft Serial Number
3. Column C - Score for the matching Shaft-Hole combination

The sum of all the items in 'Column C' would be the score for one simulation.

-------------------------------------
Some definitions:
NominalHoleShaftDifference = NominalHoleSize - NominalShaftSize
Fit = HoleDelta - ShaftDelta + NominalHoleShaftDifference
Fit > 0 is a 'Clearance Fit'.
Fit <= 0 is an 'Interference Fit'

FitLowTolerance = tightest fit allowed
FitHiTolerance = loosest fit allowed

-------------------------------------
A couple of different possible Scoring Algorithms:
Method One:
1. The score for each 'acceptable fit' = Abs(Hole1Shaft1Delta) + Abs(Hole2Shaft2Delta)
2. The score for each 'unmatched item' is 100.0
3. The lowest Score for a simulation becomes the next 'Strawman'.

Method Two Sample (use your own weighting):
FitDelta = Abs(Hole1Shaft1Delta) + Abs(Hole2Shaft2Delta)
1. Score 20 for each 'acceptable fit' with 0.0000 <= FitDelta < 0.0005
2. Score 5 for each 'acceptable fit' with 0.0005 <= FitDelta < 0.0020
3. Score 1 for each 'acceptable fit' with 0.0020 <= FitDelta < 0.0050
4. Score 20 for each 'acceptable fit' with 0.0050 <= FitDelta < 0.0100
5. Score 40 for each 'acceptable fit' with 0.0100 <= FitDelta <= FitHiTolerance
6. The score for each 'unmatched item' is 1000
7. The lowest Score for a simulation becomes the next 'Strawman'.

-------------------------------------
VBA Random Numbers seem to be a difficult concept to follow. See the code
below (tested using Excel 2003) for samples of how to:
1. Generate a sequence of different random numbers 'almost' every time.
2. Generate a sequence of the same random numbers every time.
3. Generate a sequence of random integers from 10 <= x <= 16.

-------------------------------------
Testing Suggestions:
1. Use the same sequence of random numbers each time for initial software testing.
2. Use a small sample of 3 or 4 shafts and holes, for which you know the EXACT best answer.
3. Save the 'simulation number' of the 'Final Best Answer' to give you an idea of how many
simulations are required to generate a 'Best Fit'.

'Excel VBA generates random numbers as single precision (4 bytes = 32 bits)
'which generates approximately 7 digits worth of numbers 0 <= x < 1.

Sub GenerateRandomNumbersDifferentEachTime()
  'This generates the a different set of random numbers almost every time.
  '
  'The sequence used is: Randomize(xSeed)
  '
  '                      Rnd
  '                      ...
  '                      Rnd
  '
  'To change the first random number, change the seed value
  
  Dim i As Integer
  Dim xSeed As Single
  Dim x As Single
  
  xSeed = 12345.1
  
  
  Randomize (xSeed)
    
  Debug.Print "Different sequence of Random numbers for seed " & xSeed & " started on " & Now
  For i = 1 To 5
    x = Rnd
    Debug.Print i & Format(x, "   0.0000000")
  Next i

End Sub



Sub GenerateRandomNumbersSameEachTime()
  'This generates the same set of random numbers every time.
  '
  'The sequence used is: Rnd(-1)
  '                      Randomize(xSeed)
  '
  '                      Rnd
  '                      ...
  '                      Rnd
  '
  'To change the first random number, change the seed value or change
  'the 'Rnd(-1)' to a different negative number.
  
  Dim i As Integer
  Dim xSeed As Single
  Dim x As Single
  
  xSeed = 12345.1
  
  Rnd (-1)
  Randomize (xSeed)
    
  Debug.Print "Same sequence of Random Numbers for seed " & xSeed & " started on " & Now
  For i = 1 To 5
    x = Rnd
    Debug.Print i & Format(x, "   0.0000000")
  Next i

End Sub



Sub GenerateRandomNumbersFromInteger10to16()
  'This generates the a different set of integer random numbers in the range defined
  'by 'iLowNumber' and 'iHiNumber'.
  '
  'The sequence used is: Randomize(xSeed)
  '
  '                      Rnd
  '                      ...
  '                      Rnd
  '
  'To change the first random number, change the seed value or change
  'the 'Rnd(-1)' to a different negative number.
  
  Const iLowNumber = 10
  Const iHiNumber = 16
  
  Dim i As Integer
  Dim iNumber As Integer
  Dim iNumberSpan As Integer
  Dim xSeed As Single
  Dim x As Single
  
  xSeed = 12345.1
  Randomize (xSeed)
    
  Debug.Print "Generating a different sequence of Integer Random Numbers from " & iLowNumber; " to " & iHiNumber & "."
  iNumberSpan = iHiNumber - iLowNumber + 1
  For i = 1 To 25
    x = Rnd
    iNumber = Int(iNumberSpan * x) + iLowNumber

    Debug.Print Format(i, "!@@@@@  ") & Format(iNumber, "!@@@@@")
  Next i

End Sub

Thank you very much for taking the time to help. However, this may be above my capabilities with excel. I've only worked with VB code once before. So I'm going to need some time to try and wrap my head around this.

I looked further into Monte Carlo Simulation and am still having trouble with the concept.

FDibbins my desired result would be to match serial numbers for the holes and shafts so that the deltas of their radius 1's and 2's would have the greatest clearance for the entire population. If there is a shaft that doesn't fit in a mating hole without an interference fit I would like list those parts in another column.

This problem appears to be a lot more complicated than I originally thought. There may not be an easy solution.

Your question inspired me to see if I could find a solution.

Attached are two files tested using Excel 2003:
a. ExcelForumCalculateBestFit*.xls which attempts to solve your problem.
b. ExcelForumCalculateBestFitDataTestBed*.xls which is used to generate sample
'Shaft' and 'Hole' data for use in testing.

Monte Carlo simulation uses random numbers to model a problem,
when an algorithmic solution is not possible, or when an algorithmic
solution is not practical or would take too long. In your case, the
random numbers simulate pulling a 'shaft' piece and a 'hole piece' from
separate bins with your eyes closed, and seeing if they meet the fit criteria.
If they fit, put them aside. If they don't fit, put they pieces back in their
original bins. Repeat as required until all the pieces that can have mates
have found one. Calculate a 'SCORE' based on how close the pieces match.
A LOWER SCORE is a BETTER MATCH. This is 'one Simulation Loop'.

After each 'Simulation Loop' the score for that 'Simulation Loop' is compared
to the best previous score (i.e. the 'Strawman'). If the score is lower,
that 'Simulation Loop' becomes the NEW 'Strawman'.

Simulation is COMPLETED based on one a 'User Selected' Option of:
a. Terminate at the first 'Likely Fit'. This is when all 'Shafts' have
mates, except for the NEVER MATCH 'Shafts', or when all 'Holes' have mates
except for the NEVER MATCH 'Holes'.
b. Terminate after a USER DEFINED number of 'Simulation Loops' with no new 'Strawman'. or
c. Terminate after a USER DEFINED number of Total 'Simulation Loops'

The approach I recommended has been slightly modified (improved as Engineers tend to say).
The algorithm I implemented for each 'Simulation Loop' works as follows:
a. Find all the 'Shafts' and 'Holes' that NEVER MATCH.
b. Pick a remaining 'Shaft' from the unmatched 'Shaft' bin at random.
c. Pick a remaining 'Hole' at random from the unmatched 'Hole' bin at random.
d. If both Radii in the 'Shaft' and 'Hole' fit, mark the 'Shaft/Hole' set as a match.
e. Repeat b. thru d. until all 'Shafts' and 'Holes' have mates, except for the
NEVER MATCH 'Shafts' and 'Holes' and NO MATCH 'Shafts' and 'Holes' that remain.
A NO MATCH 'Shaft' is a shaft that can NOT MATE with any of the remaining 'Holes'.
f. Calculate a SCORE for for the 'Simulation Loop' based on each of the 'Hole' to 'Shaft'
deltas.