Data comparison

[jyokom]

I have two columns of items that need to be compaired. What I need is the items in column B that do not match any of the items in column A need to be put in Column C. How can I do this? The Data in Column A can not be re arranged. Column B can be sorted.

 

[Datsmart]

jyokom,

Can you post the code as you modified it? Then maybe I can help you adjust it to do what you are asking.

What do you mean "and skip the blanks"?
Maybe if you posted a sample of your data that would make sense.

 

[jyokom]

Code is as modified:
Sub CopyB2C()
'Copy items in column J that are not in column B to column Q
lastrow = ActiveSheet.UsedRange.Rows.Count
For i = 5 To lastrow
If Not Application.WorksheetFunction.CountIf(Range("A5:A90"), Range("J" & i)) = 1 Then
Range("J" & i).Copy
Range("Q" & i).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=True, Transpose:=False
End If
Next i
End Sub

 

[jyokom]

The problem is that the results appear in the same row as the different data, ie, blank rows in between. If I sort the data A-Z, it puts the data at the bottom below many blank rows.

 

[Datsmart]

This will copy only Unique data from column J to column Q that does not exist in Column B:

Sub CopyUnique2Q()
'Clear old data from Target Column
    Range("q1:q65536").ClearContents

'Assign variables
    slr = Range("J65536").End(xlUp).Row
    lr = Range("Q65536").End(xlUp).Row
For i = 5 To slr
    'Compare column J to column B
    If Not Application.WorksheetFunction.CountIf(Range("B:B"), Range("J" & i)) = 1 Then
       'Compare column J to column J so only Unique items are copied
       If Application.WorksheetFunction.CountIf(Range("J:J"), Range("J" & i)) = 1 Then
            Range("Q" & lr) = Range("J" & i)
            lr = Range("Q65536").End(xlUp).Row + 1
       End If
    End If
Next i
End Sub

You can add code to sort the result in column Q if desired.

 

[jyokom]

When I sort the column(A-Z), it puts several blank lines above that data. Not sure why it's doing that.

 

[Datsmart]

The most common reason blank lines get "Sorted" to the top is that they are probably not truely blank. They probably have "spaces" in them.

 

[jyokom]

That last bit of code that you sent works like a champ. Thank You.
Now if I can get it to return the correct value using vlookup, I can put this to bed.