ExcelBanter - Compare 2 Worksheets Create a 3rd depending on results

How about running this against a copy of the Original worksheet. This macro
updates it in place. If it doesn't work correctly, close without saving (or
delete the copy!).

I'm guessed that you had a key column (column A in my code) that contained a
unique key per worksheet (order number/site id/Social security number...).

this code uses that unique value in column A to look for matches in both
worksheets.

Option Explicit
Sub testme()

Dim OrigWks As Worksheet
Dim NewWks As Worksheet

Dim myCell As Range
Dim DestCell As Range
Dim res As Variant
Dim iCol As Long

Dim NewKeyRng As Range
Dim OrigKeyRng As Range

Set OrigWks = ActiveWorkbook.Worksheets("Orig")
Set NewWks = ActiveWorkbook.Worksheets("New")

With OrigWks
Set OrigKeyRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
'wipe out any existing comments in column B??
OrigKeyRng.Offset(0, 1).ClearContents
End With

With NewWks
Set NewKeyRng = .Range("a2", .Cells(.Rows.Count, "A").End(xlUp))
End With

'look for deletions/changes first
For Each myCell In OrigKeyRng.Cells
res = Application.Match(myCell.Value, NewKeyRng, 0)
If IsError(res) Then
'In Orig, not New. Mark it deleted
myCell.Offset(0, 1).Value = "Cancel"
Else
'found keys in both spots
For iCol = 3 To 15
If myCell.Offset(0, iCol - 1).Value _
= NewKeyRng(res).Offset(0, iCol - 1).Value Then
'keep looking
Else
NewKeyRng(res).Resize(1, 15).Copy _
Destination:=myCell
myCell.Offset(0, 1).Value = "Changed" ' why New???
Exit For
End If
Next iCol
End If
Next myCell

'look for additions.
For Each myCell In NewKeyRng.Cells
res = Application.Match(myCell.Value, OrigKeyRng, 0)
If IsError(res) Then
'In new, not Orig. copy it over
With OrigWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

myCell.Resize(1, 15).Copy _
Destination:=DestCell

DestCell.Offset(0, 1).Value = "New"
End If
Next myCell

End Sub

(I used Changed--instead of New. You can change the code if you need to. But I
thought that it was a more accurate description.)

And if you're new to macros, you may want to read David's intro:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

(Remember it updates the Orig worksheet!

Kevin wrote:

I have a unique problem that none of the other posts or shareware seems
to solve.

I have 2 worksheets (orig.xls and new.xls) that are 15 columns wide (to
the "O"). The data is just numbers but the second column either has the
words: "new" or "cancel" which is important as you will see. I need to
compare the two worksheets and create a third worksheet (update.xls)
depending on the three possible results:

1) If a row is removed in the new.xls file = copy the row from the
orig.xls file and make the 2nd column "Cancel"

2) If a row is added to the new.xls file = copy the row from the
new.xls file and make the 2nd column for this record "New"

3) If a row has changed in any way in the new.xls file = copy the row
from the new.xls file and make the 2nd column for this record "New".

I have limited VBA/Excel skills. I wanted to make this relatively
simple but don't know where to begin! Any help or response would be
appreciated.
Kevin

--

Dave Peterson