Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have data in my excel sheet in the below format

A B
1 2
2 3
3 5
4 8
5
6
7
8
9

i.e colA has values 1 through 9 and colB has 2,3,5,8. Now what I need in col C is, the data in colA which is not there in colB .

So my result column C should have

C
1
4
6
7
9

share|improve this question

migrated from stackoverflow.com May 18 '11 at 16:08

This question came from our site for professional and enthusiast programmers.

I've haven't got excel at this computer but the basic idea is to use

=IF(ISNA(MATCH(A1, B:B, 0));A1;"")

I'll check if this works in a couple of hours.

Explanation: Set the value of the current cell to value of A1 if there exists no cell in column B that has value that is an exact match to the value of cell A1.

Documenation: MATCH, ISNA, IF

share|improve this answer
1  
Yup. Seems to work. Though I'm using a localized version of excel in which the code looks like =JOS(ONPUUTTUU(VASTINE(A2;B:B;0));A2;"") where the row 1 contains the column headers. – Aleksi Yrtiaho Feb 18 '11 at 10:04
    
Thank you very much Aleksi.. It did really worked.. Thanks alot – Identifymecnu Feb 22 '11 at 13:22

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .