Comparing two excel files

[Alexandra]

I work with large data in excel. Most of the time I need
to match two different worksheets. Both worksheets always
have two columns that contains same data (name & ID). What
would be the best way to match names and ID to obtain a
new file with data that couldn't match? if Excel good to
do that or should I use other software like Access? please
advise.

 

[Tom Ogilvy]

Look at Chip Pearson's page on working with Duplicates and Uniques
http://www.cpearson.com/excel/duplicat.htm

 

[onedaywhen]

Yes, this is the kind of task a DBMS is optimized to do. Briefly, you
would use an outer join, say LEFT JOIN, on the key column and return
rows where key column is null for the table on the right (you could
then do the equivalent RIGHT JOIN and UNION the two SELECT queries).

You can query a workbook's worksheets as if they were MS Access tables
(same provider, same syntax, etc), however you will inevitably
experience performance issues when you outer join large datasets in
Excel because it is *not* optimized for such tasks.