Identifying added data on excel file

[Diana36]

I get a weekly report that keeps adding data as well as drops off data, how can i compare last weeks report to this weeks and be able to tell wich data is new and wich data dropped of?

 

[watergate74]

Hello,

Please find below a sample script:

Sub Compare()
  Dim wst(1 To 2) As Worksheet
  Dim rng(1 To 2) As Range
  Dim intRow As Integer
  Dim intCol As Integer
  Dim varMatch As Variant
  Dim intMissing As Integer
  
  Set wst(1) = Workbooks("file1.xls").Worksheets(1)
  Set wst(2) = Workbooks("file2.xls").Worksheets(1)
  
  Set rng(1) = wst(1).Range("A1:B26")
  Set rng(2) = wst(2).Range("A1:B26")

  For intRow = 1 To Application.Min(rng(1).Rows.Count, rng(2).Rows.Count)
    varMatch = Application.Match(rng(2).Cells(intRow, 1).Value, rng(1).Columns(1), 0)
    If IsNumeric(varMatch) Then
      'Found
    Else
      'Not Found
      rng(2).Cells(intRow, 1).Resize(1, rng(2).Columns.Count).Interior.ColorIndex = 3
      intMissing = intMissing + 1
    End If
  Next intRow

  MsgBox intMissing & " missing rows"
End Sub

This simple code example identifies the rows of the 2nd file which do not occur in file 1.

If you should have more complex spreadsheets I'll recommend you the Excel program Synkronizer (http://www.synkronizer.com/). This little utility compares, merges and updates Excel files.

Regards,
Tom