Retrieve only missing records from two spreadsheet
 

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

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

regards,
ray


--
ray5_83
------------------------------------------------------------------------
ray5_83's Profile: http://www.excelforum.com/member.php...o&userid=29800
View this thread: http://www.excelforum.com/showthread...hreadid=496098

Retrieve only missing records from two spreadsheet
 

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

thanks and regards,
ray


--
ray5_83
------------------------------------------------------------------------
ray5_83's Profile: http://www.excelforum.com/member.php...o&userid=29800
View this thread: http://www.excelforum.com/showthread...hreadid=496098

Retrieve only missing records from two spreadsheet
 

"ray5_83" wrote:
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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

"ray5_83" wrote in
message ...

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

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

Retrieve only missing records from two spreadsheet
 

A sample construct is available at:
http://www.savefile.com/files/1867405
Retrieve only missing records from 2 spreadsheet_ray5_83_misc.xls
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--