Is there a way to compare 2 spreadsheets with Excel?

T

Tavish Muldoon

Is there a way to compare 2 spreadsheets?

Almost like a Unix 'diff' command.

I have several variants of certain large spreadsheets with only minor
differences - and I want to review them. Find the differences and reconcile them.

Any suggestions?

Thx.

Tmuld.
 
J

Jay

Is there a way to compare 2 spreadsheets?
Almost like a Unix 'diff' command.

I have several variants of certain large spreadsheets with only minor
differences - and I want to review them. Find the differences and
reconcile them.
Click to expand...

Do "minor differences" include adding or deleting rows or columns?

If the answer is "no," a simple way is to have a third sheet that just has
differences. For example, in Sheet3!A1 put
=IF(Sheet2!A1<>Sheet1!A1, Sheet2!A1 & " <> " & Sheet1!A1, "")
and extend for as many rows and columns as you need.

If the answer is "yes," you might try saving both as CSV files and using
MS-Word's
Tools >> Track changes >> Compare documents
This is awkward, but I'm not aware of an equivalent in Excel.
 
D

Dave Peterson

Along the same lines as Jay's suggestion...

Myrna Larson and Bill Manville have developed a compare that's very nice.

http://www.cpearson.com/excel/whatsnew.htm
look for compare.xla

But the bad news is that this does a cell-by-cell comparison. A1 compares to
A1, x99 to x99, etc.

If you insert/delete a row or column, then this won't work very well.

=======
Other alternatives that may work depending on what kind of differences you're
looking for:

Save each worksheet as a .csv file and use any comparison program you want to
compare two text files.

MSWord can compare two documents (or plain old text files), too.
 
D

Dave Peterson

You can find out if an item is in one list, but not another with a formula like:

=if(isnumber(match(a1,sheet2!a:a,0)),"Found it","not there")

And you can retrieve the tracking number (just the first one that matches the
sales order number) using =vlookup().

Take a look at Debra Dalgleish's site for instructions on how to do =vlookup().
http://www.contextures.com/xlFunctions02.html

And Chip Pearson has lots of techniques for working with duplicates (as in two
lists) at:
http://www.cpearson.com/excel/duplicat.htm
 
Top