Excel: How to compare two text strings and flag the ones

Question

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

Solution

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

Comments

[DavidPostill]

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?.

[Scott Craner]

Sounds like "Fuzzy Lookup" is what you want. Search that on the web.

[MYPA333]

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]

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

[Gary's Student]

@MYPA333 On my system I got Partial since ferrand is in both cells.

[Gary's Student]

@MYPA333 try it with data with no commas, only spaces.

[MYPA333]

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.

[Gary's Student]

@MYPA333 I'll look later..............................

[MYPA333]

Gary, thanks! Just saw the comment about the commas. Replaced the commas and left the spaces and it works like a charm! 10 times thank you! This answered my question:

[fixer1234]

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.

[MYPA333]

I'd need more help on this please. I've been obsessing on how to get this done to the point where I now run a macro for 30 minutes.

[MYPA333]

The macro does texttocolumns and then hlookups the matches and concat at the end for 300k plus rows. I've tried looping after "If a1 = a2 Then" with a For i=1 to 100 A1(i), Cells((i+1),(i+2)) = a1(i), i=i+1 End If but I can't get it to work. Please help.

[Gary's Student]

@MYPA333 ...................no rain today.......much gardening to do........I will look at this Monday..............

[MYPA333]

thank you for your help again. Got the answer from a different forum but what you gave me was the source of inspiration to learn more about VBA.