Identifying added data on excel file

Diana36

New Member
Joined
Jun 4, 2010
Messages
12
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?
 

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

watergate74

New Member
Joined
Jul 21, 2010
Messages
12
Hello,

Please find below a sample script:

Code:
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,105,950
Messages
5,508,324
Members
408,678
Latest member
ripperbolt

This Week's Hot Topics

Top