Retrieve only missing records from two spreadsheet

R

ray5_83

hi all,
first spreadsheet contain all the data backup from a database, secon
spreadsheet contain a collection of data that was backup later, howeve
due to broken pointer, some records are missing.

so is there any add-ins that can compare two spreadsheets and list ou
the missing records? i've tried the cpearson.com's compare.xla, but i
showed some of the duplicate records, which still is giving a long lis
of result...

regards,
ra
 
R

ray5_83

What i meant i want to eliminate the duplicate records, and get only th
distinct records as the result.

thanks and regards,
ra
 
M

Max

ray5_83 said:
What i meant i want to eliminate the duplicate records, and get only the
distinct records as the result.
Click to expand...

Another play to try ..

Assume we have the full backup in Sheet1, in A1:B5

Data1 Data11
Data2 Data12
Data3 Data13
Data4 Data14
Data5 Data15

and we have the "partial" backup in Sheet2, in A1:B3

Data1 Data11
Data3 Data13
Data5 Data15

(lines 2 and 4 missing)

In a new Sheet3,

Assume the key col in the comparison is col A

Put in say E1:
=IF(ISNUMBER(MATCH(Sheet1!A1,Sheet2!A:A,0)),"",ROW())
Copy down to E5
(copy down by as many lines as the data in Sheet1)

Put in A1:
=IF(ISERROR(SMALL($E:$E,ROW(A1))),"",
INDEX(Sheet1!A:A,MATCH(SMALL($E:$E,ROW(A1)),$E:$E,0)))
Copy A1 across to B1, fill down to B5
(Fill down by as many lines as the data in Sheet1)

Sheet3 will return the 2 missing lines, viz.:

Data2 Data12
Data4 Data14

Adapt to suit ..
 
Top