If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How can I compare the contents of two Excel files?
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? |
#2
|
|||
|
|||
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? |
#3
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
How do I compare two excel files in access? | SCOOBYDOO | Running & Setting Up Queries | 2 | April 5th, 2005 06:28 PM |
Mulitple files Open when Excel is launched | Roberta | Worksheet Functions | 3 | October 4th, 2004 03:56 PM |
Coverting Lotus 123 files to use with excel | Muffin1947 | General Discussion | 6 | June 20th, 2004 10:18 AM |
compare Excel files ? | Deric W | Worksheet Functions | 1 | November 6th, 2003 11:44 PM |