software

I have two spreadsheets that contain almost the same data. I want to compare
the spreadsheets and determine what is the difference between the
spreadsheets.



quot;DLopez79quot; wrote:

gt; I have two spreadsheets that contain almost the same data. I want to compare
gt; the spreadsheets and determine what is the difference between the
gt; spreadsheets.

The following macro will show the differences between sheet1 and sheet2.
Cells that are different will be listed to the side of sheet 2 table. i.e.
if the contents of b2 on sheet 1 differs from b2 on sheet 2 then b2 is listed
on sheet 2 and you can make and inspection manually.

Copy the macro into a Viual basic module (Alt F11, Insert, Module) and
press f5 to run the procedure.
Dim rng1 As Range, rng2 As Range
Dim i As Long, i2 As Long, j As Integer, j2 As Integer
Dim nr As Long, nr2 As Long, nc As Integer, nc2 As Integer

Sub compare()
Dim msg As String, count As Long, summary
' 'Select sheet 2
Sheets(quot;Sheet2quot;).Select
' set the ranges to compare
Set rng2 = Range(quot;A1quot;).CurrentRegion
Set rng1 = Sheets(quot;Sheet1quot;).Range(quot;A1quot;).CurrentRegion
nr2 = rng2.Rows.count
nc2 = rng2.Columns.count
nr = rng1.Rows.count
nc = rng1.Columns.count
count = 0
' chack thet the number of
If nr lt;gt; nr2 Then
MsgBox quot;The number of rows is differentquot;
Exit Sub
ElseIf nc lt;gt; nc2 Then
MsgBox quot;The number of Columns is differentquot;
Exit Sub
End If
For i = 1 To nr
msg = quot;quot;
For j = 1 To nc
If Cells(i, j) lt;gt; rng1.Cells(i, j) Then
'Display cells that do not agree
msg = msg amp; quot; quot; amp; Cells(i, j).Address
Cells(i, nc2 2) = msg
count = count 1
End If
Next
Next
summary = MsgBox(quot;There were quot; amp; count amp; quot; errors in the tables!quot;, , quot;N
Differences in Sheet1 amp; Sheet2quot;)
End Sub

Regards
Peter