How to compare data in each table row with rows in another table to find the best, non-exact matches

Question

I have one table that is a list of parts that are going to be unavailable and I want to find replacements for each one of the parts. I made another table of potential replacement parts that I want to compare with the first table and find the best matching alternative.

To demonstrate my issue in a much smaller scale, I threw together the following:

The above table is a list of end of life parts I want to replace as well as their respective attributes. (Note, I did not look up this data, so forgive me if it's absurd)

Then the next table is a list of alternative products with the same attribute columns.

Is there a way in Excel to compare all the specs of each orange to the specs of each apple and select the most similar apple to each orange (and thus fill in the "Similar Apple" column on the orange table)? I do not need exact matches for any of the numbers, but I want to effectively score the data in each column of each apple for each orange and choose the apple with the highest score. Preferably with weighting so that some rows have more importance than others.

I'm not too familiar with VB but I'm open to using it if I need to. If Excel can't do this, is there an alternative route that anyone would recommend?

Most solutions I found online were rather simple VLOOKUP() or INDEX() formulas which would search for a specific value in a single column or search for part of a string rather than the closest number value in multiple columns.

Answer 1

Ok, I decided to take the time and learn some VB as I realized it wouldn't be too bad of an algorithm if I could figure out how to implement it into Excel.

I'm still open to any feedback as I'm just guessing that the math is all right

Here's what I decided to write:

Sub FindApples()

'Weights for calculating scores
Const wgtShape As Double = 0.5
Const wgtHue As Double = 0.1
Const wgtSweet As Double = 0.2
Const wgtWeight As Double = 0.1
Const wgtPrice As Double = 0.1

'Set up tables
Dim oranges As ListObject
Set oranges = Sheets("Sheet1").ListObjects("Table1")
Dim apples As ListObject
Set apples = Sheets("Sheet1").ListObjects("Table2")

'Step through orange table
For Each orange In oranges.DataBodyRange.Rows

    'Collect Orange row data
    Dim shpe As String
    Dim hue As Double
    Dim sweet As Double
    Dim weight As Double
    Dim price As Double
    shpe = orange.Cells(1, 2)
    hue = orange.Cells(1, 3).Value
    sweet = orange.Cells(1, 4).Value
    weight = orange.Cells(1, 5).Value
    price = orange.Cells(1, 6).Value
    Debug.Print (orange.Cells(1, 1).Text)
    Debug.Print ("- shape:  " & shpe)
    Debug.Print ("- hue:    " & hue)
    Debug.Print ("- sweet:  " & sweet)
    Debug.Print ("- weight: " & weight)
    Debug.Print ("- price:  " & price)
    
    'Set up scoring
    Dim highScore As Double
    Dim matchName As String
    highScore = 0
    matchName = "NA"
    
    'Step through apple table
    For Each apple In apples.DataBodyRange.Rows
    
        'Collect Apple row data
        Dim appShape As String
        Dim appHue As Double
        Dim appSweet As Double
        Dim appWeight As Double
        Dim appPrice As Double
        appShape = apple.Cells(1, 2).Text
        appHue = apple.Cells(1, 3).Value
        appSweet = apple.Cells(1, 4).Value
        appWeight = apple.Cells(1, 5).Value
        appPrice = apple.Cells(1, 6).Value
        
        Dim score As Double
        score = 0
        
        'Calculate and sum up the scores
        If shpe = appShape Then
            score = score + 100 * whtShape
        End If
        If hue > 0 Then
            score = score + PercentDiff(appHue, hue) * wgtHue
        End If
        If sweet > 0 Then
            score = score + PercentDiff(appSweet, sweet) * wgtSweet
        End If
        If weight > 0 Then
            score = score + PercentDiff(appWeight, weight) * wgtWeight
        End If
        If price > 0 Then
            score = score + PercentDiff(appPrice, price) * wgtPrice
        End If
        
        'Update score if a new high score is reached
        If score > highScore Then
            highScore = score
            matchName = apple.Cells(1, 1).Value
        End If
        
        Debug.Print ("|- " & apple.Cells(1, 1).Value & " | " & score)
    Next apple
    
    If matchName <> "" Then
        orange.Cells(1, 7).Value = matchName
    End If
    
Next orange

End Sub

'std: standard value to compare against
'comp: compared value
Function PercentDiff(std As Double, comp As Double) As Double
    PercentDiff = (WorksheetFunction.Min(std, comp) / WorksheetFunction.Max(std, comp)) * 100
End Function