Return Only Different Words In Sentence

eddiegnz1

New Member
i have two columns and each column has a text sentence. I just need to create a third column that shows only the words that do not exist in both cells.

Example;
If John wants to if john doesjohn if he can might if he canwants to does he can might

<tbody>
</tbody>


kind thanks,
Eddie
 

JoeMo

MrExcel MVP
Here's a UDF you can try. After you install it, you can use it like a worksheet function as in the example below.
Excel Workbook
ABC
1If John wants to if john doesjohn if he can might if he can
Sheet10


To install the UDF:
1. With your workbook active press Alt and F11 keys. This will open the VBE window.
2. In the project tree on the left of the VBE window, find your project and click on it.
3. On the VBE menu: Insert>Module
4. Copy the UDF from your browser window and paste it into the white space in the VBE window.
5. Close the VBE window and Save the workbook. If you are using Excel 2007 or a later version do a SaveAs and save it as a macro-enabled workbook (.xlsm file extension).
6. Use the UDF as you would any native Excel function.
7. Make sure you have enabled macros whenever you open the file or the code will not run.
Code:
Function NotInBoth(S1 As String, S2 As String) As String
Dim V1 As Variant, V2 As Variant, i As Long, j As Long, d As Object
V1 = Split(S1, " ")
V2 = Split(S2, " ")
Set d = CreateObject("Scripting.Dictionary")
For i = LBound(V1) To UBound(V1)
    For j = LBound(V2) To UBound(V2)
        If LCase(V1(i)) = LCase(V2(j)) Then
            Exit For
        ElseIf j = UBound(V2) Then
            If Not d.exists(V1(i)) Then d.Add V1(i), d.Count + 1
        End If
    Next j
Next i
For i = LBound(V2) To UBound(V2)
    For j = LBound(V1) To UBound(V1)
        If LCase(V2(i)) = LCase(V1(j)) Then
            Exit For
        ElseIf j = UBound(V1) Then
            If Not d.exists(V2(i)) Then d.Add V2(i), d.Count + 1
        End If
    Next j
Next i
If d.Count > 0 Then
    NotInBoth = Join(d.keys, " ")
Else
    NotInBoth = ""
End If
End Function
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top