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?
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,133,643
Messages
5,660,044
Members
418,544
Latest member
Stefan Braem

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top