Compare multiple columns in two workbooks?

[Ed]

I regularly update a workbook that has manually inserted comments down
one column. To update, I get a new report from the database, then run
a macro that arranges and formats certain report columns into a
summary master workbook. So far, so good.

Than I have to copy across the comments. Sometimes new data is added
into or subtracted from the middle of the data range, so it's not as
simple as copying everything over and anything at the bottom is new.

As far as I can see, I would need to make a comparison string of 4 or
5 columns across a single row in the updated summary, then find this
in the previous summary and copy across the comment that is in the row
where the comparison matched. I could see a brute force matchup along
the lines of:

rwNew = count of rows in new
rwPrev = count of rows in previous
' x refers to range in new book
'y refers to range in previous book
For x = 1 To rwNew
For y = 1 To rwPrev
strNew = Values of Cells(x,4) through Cells(x,8)
strPrev = Values of Cells(y,4) through Cells(y,8)
If strPrev = strNew Then
Cell(x,10) = Cell(y,10)
Exit For
End If
Next y
Next x

Is there a better, faster, easier way to accomplish this?
Ed

 

[Tom Ogilvy]

since the data will still be sorted and in the same order, there is no reason
to nest your loops. Just progress down the new sheet and the old sheet.
Again, this assumes that the data is sorted on 5 columns in each (4 to 8 as
you show in your pseudo code).

In the code, I had 550 entries on the new sheet and 500 on the old sheet.

Sub ABC()
Dim rw As Long, shN As Worksheet
Dim shO As Worksheet
Dim cell As Range
'Set shN = Workbooks("NewDatabooks.xls").Worksheets("Data")
'Set shO = Workbooks("OldDataBook.xls").Worksheets("Data")
Set shN = Worksheets("B")
Set shO = Worksheets("A")
rw = 1 ' first row in the old data
For Each cell In shN.Range("D1550")
Do While cell >= shO.Cells(rw, 4) And _
cell.Offset(0, 1) >= shO.Cells(rw, 5) And _
cell.Offset(0, 2) >= shO.Cells(rw, 6) And _
cell.Offset(0, 3) >= shO.Cells(rw, 7) And _
cell.Offset(0, 4) >= shO.Cells(rw, 8) And rw <= 500

If cell.Value = shO.Cells(rw, 4) And _
cell.Offset(0, 1) = shO.Cells(rw, 5) And _
cell.Offset(0, 2) = shO.Cells(rw, 6) And _
cell.Offset(0, 3) = shO.Cells(rw, 7) And _
cell.Offset(0, 4) = shO.Cells(rw, 8) Then
' copy comment from shO, row rw to shN, cell.row
' next two lines were some diagostic info I used in testing
' cell.Offset(0, 9) = rw
' cell.Offset(0, 10).Value = shO.Cells(rw, "I")
rw = rw + 1
Exit Do
End If
rw = rw + 1
Loop
Next

End Sub

 

[Tom Ogilvy]

I was using two sheets in the same workbook for testing, so you only need the
appropriate references for shO (old data sheet) and shN (new data sheet).

 

[Ed]

I was using two sheets in the same workbook for testing, so you only need the
appropriate references for shO (old data sheet) and shN (new data sheet).

--
Regards,
Tom Ogilvy










- Show quoted text -

 

[Ed]

(Sorry about the blank reply!)

Sorry about the lag time in response, Tom. I lost Internet, and then
lost my computer! Didn't hae anything back up until about 3 hours
ago.

I greatly appreciate your efforts. The macro ran great for several
lines, then seemed to get off track and never came back. Thinking
about it more, I think I didn't understand my situation well enough to
present it properly, so your macro ran fine until it hit something it
wasn't designed to handle.

A better approach seems to be to run down the list of comments and
find the line in the new sheet that matches. Unfortunately, I forgot
one critical item, a report number. Without that, everything else can
be matched in several other lines.

I'm going to chunk on this for a few days, and probably won't post
back until Monday if I can't get it.

Thanks again for everything, Tom. Showing me how to link several
cells together in a comparison is great and will be critical to what I
need to do.

Ed