Comparing two columns in two separate files

[K Landsworth]

Hi, I have an excel worksheet (I will call it A.xls), its first column is
the unit numbers, I did some work with this file and saved it under a
different name (I will call it B.xls). I noticed some of the rows with unit
numbers are missing in this second file.

Could some one tell me how would I go about in comparing the first columns
of these two files so that it will give me a list of missing unit numbers
that I had in the file A.xls but now not present in B.xls

Thanks a lot in advance. - Kenny

 

[Max]

Assume source data in A.xls is in Sheet1, within A1:A20 (say)

In B.xls, source data is also in Sheet1, within A1:A10 (say)

With A.xls open as well,
Try this set up in B.xls's Sheet1,

Put in B1
=IF(COUNT(C:C)<ROW(A1),"",INDEX([A.xls]Sheet1!$A:$A,MATCH(SMALL(C:C,ROW(A1)),C:C,0)))

Put in C1
=IF([A.xls]Sheet1!A1="","",IF(ISNUMBER(MATCH([A.xls]Sheet1!A1,A:A,0)),"",ROW()))

Select B1:C1, copy down to C20, i.e. cover the extent of data in A.xls

Col B will return the required list of items in A.xls's source data which
are not found in B.xls's source. The list of items will be neatly bunched at
the top