Tags: compare, contents, excel, file, files, microsoft, msdn, old, received, software, updated, version

How can I compare the contents of two Excel files?

On Microsoft » Microsoft Excel

2,908 words with 2 Comments; publish: Thu, 22 May 2008 21:40:00 GMT; (30662.50, « »)

I have received an updated version of an Excel data file, and I would like to

compare the new file to the old to see where changes have been made. How can

I compare the contents of the two Excel files and detect differences?

All Comments

Leave a comment...

  • 2 Comments
    • I use the following to compare a block of cells on two sheets. Maybe you can

      adapt it to compare two files?

      Sub auditIt()

      '

      'this routine compares the first 50 rows/columns of sheet "Original"

      'to the same range in sheet "Updated" and marks changed cells in yellow/bold.

      'A summary is recorded in sheet "Audit" with the location of changed cells

      and the before/after values

      k = 1

      For i = 1 To 50

      For j = 1 To 50

      Sheets("Original").Select

      o = Cells(i, j)

      Sheets("Updated").Select

      u = Cells(i, j)

      If o <> u Then

      Cells(i, j).Select

      With Selection.Interior

      .ColorIndex = 6

      .Pattern = xlSolid

      End With

      Selection.Font.Bold = True

      Sheets("Audit").Select

      Cells(k, 1) = i

      Cells(k, 2) = j

      Cells(k, 3) = o

      Cells(k, 4) = u

      k = k + 1

      End If

      Next j

      Next i

      End Sub

      --

      Gary's Student

      "Geert Overbosch" wrote:

      > I have received an updated version of an Excel data file, and I would like to

      > compare the new file to the old to see where changes have been made. How can

      > I compare the contents of the two Excel files and detect differences?

      #1; Thu, 22 May 2008 21:41:00 GMT
    • Saved from a previous post...

      If the changes do not include removing rows or columns (or inserting rows or

      columns), then you could use a program written by Myrna Larson and Bill

      Manville.

      You can find a copy on Chip Pearson's site:

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

      look for compare.xla

      But remember this does a cell-by-cell comparison against two worksheets--not

      workbooks. A1 compares to A1, x99 to x99, etc. (So if you insert/delete a

      row/column, the comparison goes south very quickly.)

      Another option could be to save the worksheets (not workbooks) as a couple .CSV

      files. Then use some text comparison file to find the difference. (MSWord has

      this ability.)

      But this compares text (current values of formulas). Not the formulas

      themselves.

      And if you have a single unique key in each worksheet that should be compared,

      you could have a program that looks for matching keys and if found, does a

      comparison between the cells on those rows. (Or adds it as a new key--or marks

      it as a deleted record.)

      Geert Overbosch wrote:

      > I have received an updated version of an Excel data file, and I would like to

      > compare the new file to the old to see where changes have been made. How can

      > I compare the contents of the two Excel files and detect differences?

      --

      Dave Peterson

      #2; Thu, 22 May 2008 21:42:00 GMT