Is there a way to compare 2 spreadsheets with Excel?

[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.

 

[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.

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.

 

[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.

 

[Igor Green]

You may check our CompareIt tool (http://www.grigsoft.com/) - it can
compare excel files.

Igor Green
http://www.grigsoft.com
Compare It! + Synchronize It! : Files and folders comparison never was
easier!

 

[Guest]

Can anyone provide a solution to this scenario???

I have two worksheets with shipping information. First has sales order
numbers for our warehouse to ship and second sheet comes back to us with
sales order numbers and their respective tracking number from the warehouse.

Problem: Sometimes sales orders don't ship and I need to quickly compare the
two spreadsheets to see which sales order numbers from the first sheet don't
appear in the second sheet and therefore didn't ship.

I've scoured the Internet and can't find anything that quite works. A
formula that would seach both columns of info and record all sales order
numbers onto a 3rd spreadsheet would work best.

Thanks,

Toby

 

[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