Comparing two columns in two separate files

  • Thread starter Thread starter K Landsworth
  • Start date Start date
K

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
 
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
 
Back
Top