compare two columns

D

Dalvi

Hi,
I have two set of data in column A and B. I want to choose
a cell from column B and search for same in column A. Once
I found the number in column A I will goto next cell in
column B and do the search for matching data in columnA.
In case it could not find the matching number it should
write this missing number in column D in serial order.

I will appreciate your help in writing a formula for this
compare function.
Regards
 
M

Max

Perhaps you'd like to try this as well ..

Assume the sample data is in cols A and B,
in A1:A10 and B1:B5, as below:

100 118
101 107
102 111
103 120
104 109
105
106
107
108
109

and the objective is to compare col B with col A,
listing those numbers in col B which are not found in col A
into col D in ascending order

Select D1:D5
(range size as per col B)

Put in the formula bar:
=IF(ISERROR(SMALL(IF(COUNTIF(A1:A10,B1:B5)<1,B1:B5),ROW())),"",SMALL(IF(COUN
TIF(A1:A10,B1:B5)<1,B1:B5),ROW()))

Array-enter the formula, i.e. press CTRL+SHIFT+ENTER
instead of just pressing ENTER

For the sample data, D1:D5 will list as:

111
118
120
 
Top