Macro that compares two worksheets by ID and outputs changes

**akh** · 10-01-2010, 04:23 AM

Hey there,

I'm not 100% sure this is easy to do in Excel, but I know it's possible in MySQL.

Basically, I'm looking for a macro that can loop through two different workbooks (it's OK if I have to put the worksheet names to compare in the code manually) comparing line for line, not by line number, but by a unique ID that will be living in column A.

Ideally, afterwards this would output which lines deviate, and which column in particular does so.

If need be, this can also be put together into one workbook, and I could copy all the data into two worksheets and we could keep it all within one workbook.

The reason for this is basically comparing data to historic data and seeing if there are differences.

If my description isn't clear please let me know - I'll give whatever details I can.

Best,
Andy

**Andrew-R** · 10-01-2010, 05:23 AM

You could try this...

Sub Compare()
Const OutSheet=1
Const IDCol=1
Const FirstRow=1

Dim WB1 as Worksheet, WB2 as Worksheet
Dim FindID As Range
Dim LastRow, LastCol
Dim OutRow, RowLoop, ColLoop
Dim ThisID

Set WB1=Workbooks("MyBook.Xls").Sheets(1)
Set WB2=Workbooks("OtherBook.Xls").Sheets(1) 'Change book and sheet indexes to match your requirements

ThisWorkbook.Sheets(OutSheet).Cells.Clear
OutRow=0

LastRow=WB1.Cells(WB1.Rows.Count,IDCol).End(xlUP).Row

For RowLoop=FirstRow To LastRow
  ThisID=WB1.Cells(RowLoop,IDCol).Value
  If ThisID<>"" Then
    Set FindID=WB2.Columns(IDCol).Find(ThisID, Lookin:=xlValues, LookAt:=xlWhole)
    If FindID Is Nothing Then
      OutRow=OutRow+1
      ThisWorkbook.Sheets(Outsheet).Cells(OutRow,1).Value=ThisID & " - does not exist in both books"
    Else
       LastCol=WB1.Cells(RowLoop, WB1.Columns.Count).End(xlToLeft).Column
       For ColLoop=IDCol+1 To LastCol
          If WB1.Cells(RowLoop,ColLoop).Value<>FindID.Offset(0,ColLoop-IDCol).Value Then
            OutRow=OutRow+1
            ThisWorkbook.Sheets(Outsheet).Cells(OutRow,1).Value=ThisID & " - mismatch in column " & ColLoop
         End If
      Next ColLoop
   End If
  End If
Next RowLoop

End Sub

**akh** · 10-01-2010, 09:13 AM

Andrew - Awesome. Unfortunately it looks like im buried until the end of the day, but I'll play with it over the weekend and hopefully by Monday I'll be able to tell you how awesome this works.

Best,
Andy

**Andrew-R** · 10-01-2010, 09:27 AM

OK. It's untested and coded off the top of my head, so let me know if you find any bugs and I'll sort them out.

**akh** · 10-19-2010, 10:21 AM

Hey Andrew (or whoever else feels like helping),

After wondering like an idiot why I was getting "run time error 9 subscript out of range", I realized I had to have the two to be compared files open.

I succesfully ran the macro, and I intentionally planted differences in the two files. Unfortunately there was no output on the macro. Any idea why?

Thanks a lot in advance!

Best,
Andy