compare worksheets

I

Ivano

Hi,
I have an excel file which is updated regularly and then a backup was made.
unfortunaltely the orginal as well as the backup have been updated. is there
a utility or formula etc. that I can compare the workboor or worksheets and
tell me which cells are different. I don't even need to know what the
differences are since I can do it manually, but that that would be nice.

Thanks,
 
B

bj

i am not sure what you mean by "Different"
compared to what?
If you are lucky, track changes has been selected and you might be able to
identify them that way. otherwise, unless you have something to compare
against, I think you are out of luck.
 
D

Dave Peterson

If you wanted to look at a cell by cell comparison to compare two worksheets
cell by cell (A1 with A1, x99 with x99...), you could try this addin developed
by Myrna Larson and Bill Manville.

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

If you think that a row or column was added or deleted, this kind of comparison
soon loses its effectiveness.
 
I

Ivano

Hi bj,
we have an orginal excel file and then every month create a copy of it for
archival purposes. So there is the "working copy" which everyone should be
updating daily and then the "monthly archive" copy which is there just for
referece purposes. But, as it turns out the archive copy was being updated
as well. so now i need to find out what changes were made to the archive
copy and transfer them over to the working copy. It's a big workbook with
many tabs. So I need something to compare each cell of the workbook and
point out the different values.
 
I

Ivano

HI Dave,
Thanks for the suggestion but the compare.xla link doesn't work. Can I get
the add in anywhere else?
 
B

bj

Ok. that is different question than my initial interpretation.

here is a brute force method

first make a list of all the sheets

insert a new sheet at the beginning of the book
and select a max row, max column size pair which would cover all the data in
all of the sheets (rmax, cmax) and enter these values in the macro

enter the book names into the macro as needed
use a macro such as

Private Sub finddif()
'list of sheet names starting at A1
Dim rng As Range
dim rmax as integer
rmax = ' enter rmax here
dim cmax as integer
cmax = 'enter cmax here
dim r as integer
dim c as integer
dim rr as integer
Dim i As Integer
i=1
Set rng = Range("A1")
For Each Sheet In ActiveWorkbook.Sheets
rng.Offset(0,i).Value = Sheet.Name
shn = Sheet.Name
if i = 1 goto 99
rr = 2
for r = 1 to rmax
for c = 1 to cmax
if books("book1").sheets(shn).cells(r,c)<>
books("book2").sheets(shn).cells(r,c)then cells(i,rr)="("&r&","&c&")":rr=rr+1
next c
next r
99 i = i + 1
Next Sheet
End Sub


this will place a set of sheet names across the first row of the new sheet
and under each will list the cells which are different
..

Like I said a brute force method
 
R

Rajula

Hi,

When i click on compare.xla, i get The page cannot be found error. Says this
page was removed.

Rajula
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Embed the same Excel multiple times 0
Compare worksheet data 0
Compare difference between 2 worksheets 2003 1
Worksheet Compare 1
Compare 2 worksheets 1
Finding the proper style field and the numbered paragraph value within the style 0
Compare and merge worksheets 2
tiny discrepancies in fixed-point numbers 3

Top