excel spreadsheet compare

[chance diego]

I looking for something I could utilize that would compare two excel
spreadsheets. It would need to compare across all cells and note any
changes. There will be text and numbers contained within the cells.

I've seen some stuff posted on here, but it compares (I.E cell A1 to cell
A1). The excel spreadsheets I'll be reviewing will have adds, updates and
deletes. So it needs to compare based on a unique identifier (probably name
in first column) and then note if there are any changes. Also, note if the
data between the two excel spreadsheets is new or deleted data.

thanks for any help you can provide..

 

[chance diego]

anyone?

 

[Dave Peterson]

If the number of columns times 2 (or 3) would fit in the worksheet, then I'd do
this:

Create a new worksheet
Copy the unique ID from one worksheet into column A
Copy the unique ID from the other worksheet at the bottom of this list.

The use data|Filter|advanced filter to get unique values from that single
column.
http://contextures.com/xladvfilter01.html#FilterUR
and
http://www.contextures.com/xlVideos04.html#AdvFilt2003
(Both from Debra Dalgleish's site)

Then use a bunch of =vlookup()'s or =index(match())'s to return the data to be
compared (two columns--one for each worksheet). And a third column that would
indicate the differences.

 

[MyVeryOwnSelf]

I looking for something I could utilize that would compare two excel

spreadsheets. It would need to compare across all cells and note any
changes. There will be text and numbers contained within the cells.

I've seen some stuff posted on here, but it compares (I.E cell A1 to
cell A1). The excel spreadsheets I'll be reviewing will have adds,
updates and deletes. So it needs to compare based on a unique
identifier (probably name in first column) and then note if there are
any changes. Also, note if the data between the two excel
spreadsheets is new or deleted data.

This is far from elegant, but it might be useful.

You could save both as csv files, open one in MS-Word, and use Word's
feature:
Tools > Compare and merge documents

 

[Gord Dibben]

MVOS

Is a spreadsheet a worksheet or a workbook?

The add-in, Compare.xla written by Myrna Larson and Bill Manville, allows
you to compare the contents of two workbooks and highlight differences.

http://www.cpearson.com/excel/whatsnew.htm

Look for Compare.xla at 14-June-2000

Download Compare.ZIP, unzip Compare.xla and add it with rest of your your
add-ins.

On Tools Menu, select add-ins and check in (tick in)Compare Worksheets.
From tools menu then select Compare Sheets and follow instructions.


Gord Dibben Excel MVP