i want to compare data in list 1 in column A with list 2 in column B and make
a column C which shows data which is not in A

A B C
apple apple cat
cat ball rubber
ball dog
rubber
dog

Try the following...

C1, copied down:

=INDEX($A$1:$A$5,SMALL(IF(COUNTIF($B$1:$B$3,$A$1:$ A$5)=0,ROW($A$1:$A$5)-R
OW($A$1)+1),ROWS($C$1:$C1)))

....confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

In article ,
"ykas" wrote:

i want to compare data in list 1 in column A with list 2 in column B and make
a column C which shows data which is not in A

A B C
apple apple cat
cat ball rubber
ball dog
rubber
dog

you could try following macro
Sub Macrounique()

For Each cell In Range("a:a")
Range("b:b").Select
On Error GoTo myerr
Selection.Find(What:=cell, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).Activate
Next cell
myerr:
Range("c5536").End(xlUp).Select
ActiveCell.Offset(1, 0) = cell.Value
Resume Next
End Sub

"ykas" wrote:

i want to compare data in list 1 in column A with list 2 in column B and make
a column C which shows data which is not in A

A B C
apple apple cat
cat ball rubber
ball dog
rubber
dog