Retrieve only missing records from two spreadsheet

[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

 

[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

 

[Max]

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

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 ..

--

 

[Max]

A sample construct is available at:
http://www.savefile.com/files/1867405
Retrieve only missing records from 2 spreadsheet_ray5_83_misc.xls