Comparison string or words between two columns

[lalro143]

Hi,

I have two columns in which we have different words.

Now I need to compare Col A with Col B and find out the unmatched word of Col B in Col C.

In below case "Hi" is the word which is not in Col A.

For Example

Col A Col B Col C (Output Unmatched Words)
Hello Vijay Kumar Kumar Hello Vijay Hi Hi

Thanks in advance

 

[hiker95]

Hi,

I have two columns in which we have different words.

Now I need to compare Col A with Col B and find out the unmatched word of Col B in Col C.

In below case "Hi" is the word which is not in Col A.

For Example

Col A Col B Col C (Output Unmatched Words)
Hello Vijay Kumar Kumar Hello Vijay Hi Hi

Thanks in advance

lalro143,

Are you looking for a formula solution, or, a macro?

By your description, I am not able to determine where your actual raw data is, and, what the results should look like.


See reply #2 at the next link, if you want to show small screenshots, of the raw data, and, what the results should look like.

http://www.mrexcel.com/forum/about-board/508133-attachments.html#post2507729


Or, you can post your workbook/worksheets to the following free site (sensitive data changed), mark the workbook for sharing, and, provide us with a link to your workbook:

https://dropbox.com

 

[lalro143]

Cell A2- Hello Vijay Kumar

Cell B2- Kumar Hello Vijay Hi

Cell C2- Hi

Above you can see cell A2 has some words which needs to be compared with cell B2 words. Above you can see only "Hi" is the word which is not in Cell A2 but it is in Cell B2. So my final output is in cell C2=Hi.

This is only sample. Have to do the same activity with 50000 cells. A Macro would be great help.[TABLE="width: 310"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]

[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 310"]
<colgroup><col><col><col></colgroup><tbody></tbody>[/TABLE]

 

[Peter_SSs]

See if this macro does what you want. If not, please explain & give examples that demonstrate the issues. One example wasn't a lot to go on.

Test in a copy of your workbook.

Sub UnmatchedWords()
  Dim d As Object
  Dim a As Variant, b As Variant, aWords As Variant, wrd As Variant
  Dim i As Long
  
  Set d = CreateObject("Scripting.Dictionary")
  d.CompareMode = 1
  a = Range("A2", Range("B" & Rows.Count).End(xlUp)).Value
  ReDim b(1 To UBound(a), 1 To 1)
  For i = 1 To UBound(a)
    d.RemoveAll
    For Each wrd In Split(a(i, 2))
      d(wrd) = 1
    Next wrd
    For Each wrd In Split(a(i, 1))
      If d.exists(wrd) Then d.Remove wrd
    Next wrd
    b(i, 1) = Join(d.keys)
  Next i
  Range("C2").Resize(UBound(b)).Value = b
End Sub

Results with my small dummy data:

Excel Workbook
	A	B	C
1	Data 1	Data 2
2	Hello Vijay Kumar	Kumar Hello Vijay Hi	Hi
3	Good day	Day Good
4	hello world	green red	green red
5	red black white	black blue red green	blue green
Unmatched words

 

[lalro143]

Thanks Peter. It is working fine with my data.

 

[Peter_SSs]

Thanks Peter. It is working fine with my data.

Good news. Thanks for letting us know.