Finding The Missing Data

B

Biruzz

I have 2 worksheets in a file. The first worksheet has six thousand
rows and 10 columns and the second sheet has only 4593 rows and 8
columns. All the names on the second sheet are already on the first
sheet. I need a quickway to find all the missing names and to insert
these names that are missing from the second sheet into the second
sheet.
 
M

Max

One play ..

Assume the 1st sheet is named as: X, data in cols A to J, from row1 down,
where the key col is col A (names). Assume the 2nd sheet is named as: Y,
data in cols A to H, from row1 down, where the key col is also col A
(names).

In a new sheet,

Put in A1:
=IF(X!A1="","",IF(ISNUMBER(MATCH(X!A1,Y!A:A,0)),"",ROW()))

Put in B1:
=IF(ROW()>COUNT($A:$A),"",INDEX(X!A:A,SMALL($A:$A,ROW())))

Copy B1 across 10 cols to K1. Select A1:K1, fill down to cover the extent of
data in X, ie down by 6000 rows to K6000. Cols B to K will return only the
missing lines from X, ie lines with names not found in Y, with all results
neatly bunched at the top. You could then just select the result lines & do
a copy n paste special as values below the data in Y, assuming of course,
that the first 8 of the 10 cols in X are identical to the 8 cols in Y.
 
Top