Excel help

danswan · 11 Dec 2009 at 14:06

Does anyone know of a quick way for excel to do the following:

I have two columns, both contain numbers. I want Excel to tell me numbers that exist in column B, which do NOT exist in column A. I'm thinking there has to be a quick way of doing this, but I'm pretty poor in excel - I'm using version 2003 btw.

Any help gratefully received

div0 · 11 Dec 2009 at 14:34

There's a few ways you could do this.

Easiest is probably, in cell C1 add the following:

=IF(COUNTIF(A:A,B1)>0,"",B1)

This basically counts how many times B1 is found in Column A. If B1 is found (ie countif result is greater than 0) then C1 is filled in as 'blank'. If B1 was not found in Column A, then C1 shows the value of B1.

If you fill this formula down Column C, it will give you all the values found in Column B, which are not in Column A.

SiriusB · 11 Dec 2009 at 14:40

=IF(ISERROR(VLOOKUP(B2,$A$2:$A$11,1,FALSE)),"NOT PRESENT IN A","")

You might have to tweak the values to suit. Once you have done it, copy the formula down to the other cells in column C.

The first value [B2] looks at that value in B2 then tries to find it in the whole of column A.

You can also replace "NOT PRESENT IN A" with B2 if you want it to display the value that is missing from A.

EDIT: Damn too slow.

danswan · 11 Dec 2009 at 14:41

Superb! Thanks, that's exactly what I need

*edit* Both of you!

SiriusB · 11 Dec 2009 at 14:44

You're welcome! I think div0's is a bit neater, so go with his!

div0 · 11 Dec 2009 at 14:49

SiriusB said:
You're welcome! I think div0's is a bit neater, so go with his!

They both should give the same result. Usually the main difference between the various solutions is down to what you want to do with the result once you've got it. But in this case, they should both work out identical for the OP.