Faster way to do this?

[Ed]

I periodically extract data from report files to populate a worksheet.
Every week or so, I rerun this to create an updated workbook. It's starting
to get rather large - each update adds a few more new rows. My problem is
that the data in any row can change from the previous workbook.

I can check for changes by comparing the values of certain cells between the
workbooks. Right now, the method I'm thinking of would look like:
For Each cell in Book1ColA
Get Book1ColA Value
Get Book1ColA Row#
Find Book1ColA Value in Book2ColA
Get Book2ColA Row#
If Value Book1ColC Row# <> Value Book2ColC Row# Then
MsgBox or something
End If
Next cell

Is there a faster way to accomplish this, versus iterating through every
cell in the column?

Ed

 

[Toppers]

Ed,
I don't think there is a faster approach than comparing each cell;
however, you can make the comparison (much) faster by reading the data
(ranges) into arrays and comparing the arrays rather than looping through
the ranges.

HTH

 

[Ed]

Toppers: Using arrays does sound much faster, but I am very unfamiliar with
them. Part of the problem is that I would have to compare one set of
values, then if they agree compare a second set of values, and if they don't
agree then do something. So the array then would have to be three
dimensional?? Also, the new rows added are not always at the bottom of the
list - they could be anywhere in the range. Value xyz might have been in
Row 3 last week, and is in Row 5 this week.

Ed

 

[K Dales]

Here is a "non-code" solution, depending on whether you can tolerate an
additional (potentially hidden) column in the workbook:
This formula will find the row of the "master" worksheet that contains the
value in column A and see if it is on the same row or not (will write this
formula as if I am putting it on row 2; doesn't matter which column) - this
assumes you are looking for unique matches, but from the way you dexcribed
the problem I am guessing that is true:
=(MATCH(A2,[MasterBookName]WorksheetName!$A:$A,0)=ROW())
You could use this formula itself as a "flag" for the changed rows, or you
could use it as the basis for conditional formatting to highlight those rows.
Would be nice simply to use this formula in the conditional format, but
conditional formatting does not allow references to other sheets/books.

 

[Ed]

Thank you. I'll play with this and see what I can do. I appreciate the
boost.
Ed

Here is a "non-code" solution, depending on whether you can tolerate an
additional (potentially hidden) column in the workbook:
This formula will find the row of the "master" worksheet that contains the
value in column A and see if it is on the same row or not (will write this
formula as if I am putting it on row 2; doesn't matter which column) - this
assumes you are looking for unique matches, but from the way you dexcribed
the problem I am guessing that is true:
=(MATCH(A2,[MasterBookName]WorksheetName!$A:$A,0)=ROW())
You could use this formula itself as a "flag" for the changed rows, or you
could use it as the basis for conditional formatting to highlight those rows.
Would be nice simply to use this formula in the conditional format, but
conditional formatting does not allow references to other sheets/books.
--
- K Dales

I periodically extract data from report files to populate a worksheet.
Every week or so, I rerun this to create an updated workbook. It's starting
to get rather large - each update adds a few more new rows. My problem is
that the data in any row can change from the previous workbook.

I can check for changes by comparing the values of certain cells between the
workbooks. Right now, the method I'm thinking of would look like:
For Each cell in Book1ColA
Get Book1ColA Value
Get Book1ColA Row#
Find Book1ColA Value in Book2ColA
Get Book2ColA Row#
If Value Book1ColC Row# <> Value Book2ColC Row# Then
MsgBox or something
End If
Next cell

Is there a faster way to accomplish this, versus iterating through every
cell in the column?

Ed

 

[Toppers]

Ed,
Here is some sample code based on your "pseudo code". It could easily
be modified to include further checks.

Sub Match2Master()

Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Dim wb1 As Workbook, wb2 As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim wb1n As String, wb2n As String
Dim x As Variant, y As Variant
Dim r1 As Long, r2 As Long
Dim lastrow As Long, res As Variant


Set wb1 = Workbooks("Book1")
Set wb2 = Workbooks("Book3")
wb1n = wb1.Name
wb2n = wb2.Name

With wb1
Set ws1 = .Worksheets("Sheet1")
With ws1
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng1 = .Range("A1:C" & lastrow)
End With
End With
With wb2
Set ws2 = .Worksheets("Sheet1")
With ws2
lastrow = .Cells(Rows.Count, "A").End(xlUp).Row
Set Rng2 = .Range("A1:C" & lastrow)
Set Rng3 = .Range("A1:A" & lastrow)
End With
End With

' Read the data into the variants
x = Rng1
y = Rng2

' Loop through the variant array for workbook 1
For r1 = 1 To UBound(x, 1)
' Look for match in Col A of second workbook
res = Application.Match(x(r1, 1), Rng3, 0)
If IsError(res) Then
MsgBox "No match found for value " & x(r1, 1) & " in workbook "
& wb2n
Else
r2 = res ' row number in second workbook
If x(r1, 3) <> y(r2, 3) Then ' Column C does not match
MsgBox "Value for row " & r1 & " in workbook " & wb1n _
& " does not match value for row " & r2 & " in workbook " &
wb2n
End If

End If

Next r1


End Sub

HTH