Comparing Words in Cell and Showing Difference

Hello,

I am trying to compare a sequence of words between two cells and show the difference, separated by a comma.

For Example:

Cell A has: The quick brown dog jumped over the lazy dog
Cell B has: The brown mouse jumped over the lazy cat and ran


Desired Result: Column D: NEW WORDS in B not in A & Column E: Original Words in A Not in B


The desired result is in Columns D & E. (Please see attached spreadsheet)

Thanks for you're help.

Can do this with a user defined function. For example

Function NewWordsInB(A As Range, B As Range) As String
AArray = Split(LCase(A), " ")
BArray = Split(LCase(B), " ")
For BWord = 0 To UBound(BArray)
    If Len(BArray(BWord)) > 0 Then
        BWordNew = True
        For AWord = 0 To UBound(AArray)
            If BArray(BWord) = AArray(AWord) Then
                BWordNew = False
                Exit For
            End If
        Next AWord
        If BWordNew = True Then
            NewWordsInB = NewWordsInB & BArray(BWord) & ","
        End If
    End If
Next BWord
If Len(NewWordsInB) > 0 Then NewWordsInB = Left(NewWordsInB, Len(NewWordsInB) - 1)
End Function

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter = NewWordsInB(A2,B2)

Remember to save the workbook as a macro enabled workbook .xlsm

Can do this with a user defined function. For example

Function NewWordsInB(A As Range, B As Range) As String
AArray = Split(LCase(A), " ")
BArray = Split(LCase(B), " ")
For BWord = 0 To UBound(BArray)
    If Len(BArray(BWord)) > 0 Then
        BWordNew = True
        For AWord = 0 To UBound(AArray)
            If BArray(BWord) = AArray(AWord) Then
                BWordNew = False
                Exit For
            End If
        Next AWord
        If BWordNew = True Then
            NewWordsInB = NewWordsInB & BArray(BWord) & ","
        End If
    End If
Next BWord
If Len(NewWordsInB) > 0 Then NewWordsInB = Left(NewWordsInB, Len(NewWordsInB) - 1)
End Function

Open the VBA editor by hitting Alt F11.
Insert a new module with Insert - Module
Paste in the above function
Go back to the sheet by hitting Alt F11.

In a suitable cell, enter = NewWordsInB(A2,B2)

Remember to save the workbook as a macro enabled workbook .xlsm

Here's the code for the other function

Function OriginalWordsInA(A As Range, B As Range) As String
AArray = Split(LCase(A), " ")
BArray = Split(LCase(B), " ")
For AWord = 0 To UBound(AArray)
    If Len(AArray(AWord)) > 0 Then
        AWordNew = True
        For BWord = 0 To UBound(BArray)
            If AArray(AWord) = BArray(BWord) Then
                AWordNew = False
                Exit For
            End If
        Next BWord
        If AWordNew = True Then
            OriginalWordsInA = OriginalWordsInA & AArray(AWord) & ","
        End If
    End If
Next AWord
If Len(OriginalWordsInA) > 0 Then OriginalWordsInA = Left(OriginalWordsInA, Len(OriginalWordsInA) - 1)
End Function

Hi,

enter

=newwords(A1,B1) in a cell where A1 & B1 contain the two strings.

One way

Function newwords(st As String, st2 As String)
    Dim st_1, st_2, x As Long, y As Long, stNew As String, c As Long
    st_1 = Split(st, " ")
  
    For x = 0 To UBound(st_1)
        If InStr(st2, st_1(x)) = 0 Then
            stNew = stNew & " " & st_1(x)
        End If
    Next x
    newwords = stNew

End Function

Hi,

Swapping them around seems to work for me and =newwords(b2,a2) is returning 'tests' & 'quiz'

I agree the UDF is not distinguishing plurals like 'tests' from the singular 'test'. At the moment as long as the UDF finds a word string in one cell anywhere in the other string then it's not counting that as a different word.

However does not Martin's UDF in post#6 give you exactly what you want - give or take the odd typo like 'orginal'

A small modification to Martin's code will do it.

Function SameWords(A As Range, B As Range) As String
AArray = Split(LCase(A), " ")
BArray = Split(LCase(B), " ")
For AWord = 0 To UBound(AArray)
    If Len(AArray(AWord)) > 0 Then
        AWordNew = True
        For BWord = 0 To UBound(BArray)
            If AArray(AWord) = BArray(BWord) Then
                AWordNew = False
                Exit For
            End If
        Next BWord
        If AWordNew = False Then 'True Then
            SameWords = SameWords & AArray(AWord) & ","
        End If
    End If
Next AWord
If Len(SameWords) > 0 Then SameWords = Left(SameWords, Len(SameWords) - 1)
End Function

Thanks Richard, OIC, so i add another module and then put in column D2 =samewords(A2,B2).

Works great. Do you know how to modify the other scripts so it puts a comma "," after each word?


Ok I got it, put a &"," like stNew = stNew & " " & st_1(x)&","