Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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... |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 -- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
AUTOMATIC way to copy the value of a cell in one spreadsheet | Excel Worksheet Functions | |||
Mirror spreadsheet? | Excel Discussion (Misc queries) | |||
Inserting and Tracking Missing Sequence | Excel Discussion (Misc queries) | |||
i have rows missing on my spreadsheet but i cannot unhide them? | Excel Discussion (Misc queries) | |||
Can I make one spreadsheet retrieve info from previous spreadshee. | Excel Discussion (Misc queries) |