compare columns

[ned]

hi all

I have column A and B in the same wsheet. I need to scan each cell in
column A and find if the cell in A matches with any cell in column C ,
if it does not match any of the cells in B, I want to write the text of
the A cell to column C.

Where can I find some examples?


thanks

 

[Dan R.]

Ned,

Here's one way:

Sub Test()
Dim lRow As Long
Dim rng As Range, i As Range, xRng As Range

lRow = Range("A65536").End(xlUp).Row
Set rng = Range(Cells(1, 2), Cells(lRow, 2))

For Each i In rng
Set xRng = rng.Find(What:=i.Offset(0, -1).Value, _
LookIn:=xlValues, MatchCase:=False)
If xRng Is Nothing Then
i.Offset(0, -1).Copy i.Offset(0, 1)
End If
Next i

End Sub

 

[Bill Renaud]

This could be done with a simple worksheet function. With data in columns
$A and $B, put the following formula in cell $C$1 and fill down the extent
of the range. This example has data in $A$1:$B25. Cells in column $A that
can be found in column $B will have a blank string ("") in column $C.

=IF(ISNA(MATCH(A1,$B$1:$B$25,0)),A1,"")