Compare 2 columns and remove duplicates

[viper20]

Hello,

I have 2 columns A and B column A has about 700 rows and column B has about 400 rows. Some of the data appears in both column A and B so what i want to do is take any of the data that are not duplicates and move them to column C, the data in the 2 fields does not directly match up from left to right not sure if i explained that right but i will provide an example to demonstrate what i mean:

Column A Column B

apple watermelon
orange peaches
grape apple
watermelon kiwi
blueberries
strawberries
mango
peaches
kiwi
pineapple

So how can i get the unique fields from column A to appear in Column C?

Thanks guys

 

[petes]

If your data always has column B longer than column A, you could just swap all column references in the code. Is that your circumstance or can either column be the longer one?

Also, in that original sample data, all the values in the shorter list appeared in the longer list. Is that your circumstance too or can either column contain unique entries?

Either column can be longer one OR Either column can contain unique entries. I just need to match column A with Column B and pull unique entries from Column A only. Hope I am clear.

 

[Peter_SSs]

Either column can be longer one OR Either column can contain unique entries. I just need to match column A with Column B and pull unique entries from Column A only. Hope I am clear.

It is late here so I have done a quick adaptation of that earlier code & not tested it much. See how it goes in a copy of your workbook.

Sub Test()
  Dim c As Range, d As Range, LRA As Long, LRB As Long, i As Long
  LRA = Range("A" & Rows.Count).End(xlUp).Row
  LRB = Range("B" & Rows.Count).End(xlUp).Row
  
  For Each c In Range("A1:A" & LRA)
      If Not WorksheetFunction.CountIf(Range("B1:B" & LRB), c.Value) >= 1 Then
          i = i + 1
          Range("C" & i).Value = c.Value
      End If
  Next c
End Sub

 

[petes]

Sub Test()
  Dim c As Range, d As Range, LRA As Long, LRB As Long, i As Long
  LRA = Range("A" & Rows.Count).End(xlUp).Row
  LRB = Range("B" & Rows.Count).End(xlUp).Row
  
  For Each c In Range("A1:A" & LRA)
      If Not WorksheetFunction.CountIf(Range("B1:B" & LRB), c.Value) >= 1 Then
          i = i + 1
          Range("C" & i).Value = c.Value
      End If
  Next c
End Sub

Thanks a lot! I quickly ran this code for few samples and it is working fine

 

[Peter_SSs]

Thanks a lot! I quickly ran this code for few samples and it is working fine

You're welcome.