-1

I have a huge file that is similar to the one below. I'd like to be able to compare the group of words in column A with the group of words in column B and get "No Match", "Exact Match" or "Partial Match" in column C. I'm basic user with formulas and can use VBA to save and run macros.

Small notes, the columns do have duplicates and the comparison needs to be done only on cell by cell (E.g A1 with B1). Even a simpler form of this will help. Thank you.

A - classement fifa, classico barca real, clavier arabe, clermont ferrand

B - green grass soccer, hotel europa, clavier arabe, ferrand

C - No Match, No Match, Exact Match, Partial Match

  1. I've tried variations of VLOOKUP and SEARCH but without results.
  2. I've tried Text to Columns and then VLOOKUP the 2 sets of texts but it will still show partial data which I can't use.
  3. The closest answer I could get was How can I compare two columns in Excel to highlight words that don't match? but when I run the macro for all my columns, it ignores the duplicates and doesn't compare A1 with B1, it seems to check A:A with B:B and I still need to manually check for No Match, Exact Match and Partial Match which is extremely time consuming.

I would like to match whole words with partial words when searching for partial matches -

Cup vs Cupboard = Partial
Cupcake vx Cupboard = No Match
3
  • 2
    Please note that Super User is not a script writing service. If you tell us what you have tried so far (including any scripts you are using) and where you are stuck then we can try to help with specific problems. You should also read How do I ask a good question?.
    – DavidPostill
    May 9, 2016 at 10:10
  • Sounds like "Fuzzy Lookup" is what you want. Search that on the web. May 9, 2016 at 15:43
  • Just tried Fuzzy Lookup but the similarity match is too high to get actual, good data. It stays around .89 where 1 is exact match, even for clear mismatches.
    – MYPA333
    May 9, 2016 at 16:34

2 Answers 2

1

Try this small UDF():

Public Function comparee(s1 As String, s2 As String) As String
    comparee = "No Match"
    If s1 = s2 Then
        comparee = "Exact match"
        Exit Function
    End If

    arr1 = Split(s1, ", ")
    arr2 = Split(s2, ", ")
    For Each a1 In arr1
        For Each a2 In arr2
            If a1 = a2 Then
                comparee = "Partial"
                Exit Function
            End If
        Next a2
    Next a1
End Function

enter image description here

10
  • Tried the formula but I only get No Match or Exact Match. The way the data is set in the table is A1: Classement fifa, B1 green grass soccer. A2: classico barca real, B2: hotel europa etc. Are the formulas working for you or am I doing something completely off? 10x
    – MYPA333
    May 9, 2016 at 16:36
  • @MYPA333 On my system I got Partial since ferrand is in both cells. May 9, 2016 at 16:54
  • @MYPA333 try it with data with no commas, only spaces. May 9, 2016 at 16:56
  • I'll give this another try, I probably missed something. Pls. also see the logic I used to get this rudimentary solution. If you have any suggestions on how to put that in a VBA module, that would be awesome.
    – MYPA333
    May 10, 2016 at 10:57
  • @MYPA333 I'll look later.............................. May 10, 2016 at 11:03
0

Here's a solution I found on another forum:

Function GetMatches(s1 As String, s2 As String)
    Dim spl1 As Variant, spl2 As Variant, i As Long

    If s1 = s2 Then GetMatches = "Exact Match": Exit Function

    spl1 = Split(Application.Trim(s1))
    spl2 = Split(Application.Trim(s2))

    For i = 0 To UBound(spl1)
           If Not IsError(Application.Match(spl1(i), spl2, 0)) Then GetMatches = GetMatches & " " & spl1(i)
    Next i
    If Len(GetMatches) = 0 Then
        GetMatches = "No Match"
    Else
        GetMatches = Application.Trim(GetMatches)
    End If
End Function
1
  • Try using FIND. You will probably need to do FIND of A in B and B in A, assuming either could be the partial of the other.
    – fixer1234
    May 11, 2016 at 7:43

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .