comparing columns

felber · Mar 26th, 2002, 05:34 PM

I often have two long columns of data, of varying length, that I need to compare. I need to take each cell in B, see if it matches any cells in A, and if it doesn't put the contents of B into C. I am new to excel and would appreciate any help. Thanks.

BabyTiger · Mar 26th, 2002, 05:43 PM

Hi,

You might want to try the mixed Vlookup and IF functions:

=if(iserror(vlookup(b1,$A$1:$A$10,1,false)),b1,vlookup(b1,$A$1:$A$10,1,false))

The above formula is entered into c1, and copy it all the way down until there's no values in column b.

Meaning of the above formula is, if looking up value in B1, exists in column A or range A1 to A10, then retrun the value from Column A, otherwise return the value from B1.

Once the formula is copied down, the reference cell b1 should change, ie formula in c2 would reference to b2, so on.

HTH

RichardS · Mar 26th, 2002, 07:16 PM

Sorry, BabyTiger, but I think what is required in C is the value in B if B is not in A, otherwise nothing. If so, try this
=IF(ISNA(MATCH(B1,$A$1:$A$1000,0)),B1,"") in Cl.
Change $A$1000 to the range you need, and copy down as far as the bottom of column B data.

The again, I could be barking up the wrong tree.

Richard

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 26th, 2002, 05:34 PM	#1
felber New Member Join Date: Mar 2002 Posts: 1	I often have two long columns of data, of varying length, that I need to compare. I need to take each cell in B, see if it matches any cells in A, and if it doesn't put the contents of B into C. I am new to excel and would appreciate any help. Thanks.

Mar 26th, 2002, 05:43 PM	#2
BabyTiger Board Regular Join Date: Mar 2002 Location: Wellington Posts: 104	Hi, You might want to try the mixed Vlookup and IF functions: =if(iserror(vlookup(b1,$A$1:$A$10,1,false)),b1,vlookup(b1,$A$1:$A$10,1,false)) The above formula is entered into c1, and copy it all the way down until there's no values in column b. Meaning of the above formula is, if looking up value in B1, exists in column A or range A1 to A10, then retrun the value from Column A, otherwise return the value from B1. Once the formula is copied down, the reference cell b1 should change, ie formula in c2 would reference to b2, so on. HTH

Mar 26th, 2002, 07:16 PM	#3
RichardS Board Regular Join Date: Feb 2002 Location: Victoria, Australia Posts: 761	Sorry, BabyTiger, but I think what is required in C is the value in B if B is not in A, otherwise nothing. If so, try this =IF(ISNA(MATCH(B1,$A$1:$A$1000,0)),B1,"") in Cl. Change $A$1000 to the range you need, and copy down as far as the bottom of column B data. The again, I could be barking up the wrong tree. Richard