Search code examples
excelexcel-formulaexcel-2010excel-2007worksheet-function

How to compare two columns in MS excel?

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.sstatic.net/k0K9a.jpg

THANK YOU VERY MUCH..

Solution

  • 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.

    enter image description here