If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
MS Excel 2000 - Compare two lists of data.
I have two different files, both use the same unique identifiers. How can I
ascertain which records are missing from the smaller file. For example: File A, has 28,225 different records. File B, has 14,205 different records. Which records from File A, are missing from File B? Thank you, Jerry |
#2
|
|||
|
|||
MS Excel 2000 - Compare two lists of data.
Hi,
if you apply a vlookup then everything that shows as #N/A means that are missing from the other list Let' say your File A is in sheet1 and File B in sheet2 starting in cell A2, in B2 sheet1 enter =VLOOKUP(A2,Sheet2!A1:A25000,1,FALSE) "Jerry" wrote: I have two different files, both use the same unique identifiers. How can I ascertain which records are missing from the smaller file. For example: File A, has 28,225 different records. File B, has 14,205 different records. Which records from File A, are missing from File B? Thank you, Jerry |
#3
|
|||
|
|||
MS Excel 2000 - Compare two lists of data.
Eduardo, than you for your quick reply. I would have responded sooner I have
been having a hard time trying to make your solution work, and was unsuccessful -all, came back "#N/A". I must be doing something wrong. Could you please elaborate, I am not an Excel power user. Thanks. "Eduardo" wrote: Hi, if you apply a vlookup then everything that shows as #N/A means that are missing from the other list Let' say your File A is in sheet1 and File B in sheet2 starting in cell A2, in B2 sheet1 enter =VLOOKUP(A2,Sheet2!A1:A25000,1,FALSE) "Jerry" wrote: I have two different files, both use the same unique identifiers. How can I ascertain which records are missing from the smaller file. For example: File A, has 28,225 different records. File B, has 14,205 different records. Which records from File A, are missing from File B? Thank you, Jerry |
#4
|
|||
|
|||
MS Excel 2000 - Compare two lists of data.
.. and was unsuccessful -a lot of false "True" ..
You're probably hit by data inconsistency, either text nums vs real nums issue and/or extraneous white spaces for textstring matches. It's quite common. Paste some representative sample data for both the lookup values and the reference col values where there are apparent matches which are not being returned correctly. -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- |
Thread Tools | |
Display Modes | |
|
|