How to compare two columns in MS excel?

Question

I have two columns of data in excel sheet- column A and column B. I want that the values which are in column a but not in column b, should appear column c. for example please look at this screen shoot- https://i.stack.imgur.com/k0K9a.jpg

THANK YOU VERY MUCH..

Answer 1

You can use this array formula in C2(it must be in C2 or the reference of $C$1:C1 must be changed to the cell directly above the first instance of this formula):

=IFERROR(INDEX($A$1:$A$10,MATCH(1,(COUNTIF($A$1:$A$10,$A$1:$A$10)<>COUNTIF($B$1:$B$6,$A$1:$A$10))*(COUNTIF($C$1:C1,$A$1:$A$9)<COUNTIF($A$1:$A$10,$A$1:$A$10)-COUNTIF($B$1:$B$6,$A$1:$A$10)),0)),"")

Being an array formula it must be confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode. If done correctly then Excel will put {} around the formula.

Then copy/drag the formula down till you get blanks.