How to update a portion of rows and columns between two files

  • Thread starter Thread starter Laurie
  • Start date Start date
L

Laurie

I have a Master file with 20 columns of data for each of approximately
700 rows. Each month, I receive an update file that I need to use to
update 10 columns of data for 3/4 of the rows in my Master file.
There is a unique identifier in each file. Not all rows in the update
file will be copied into the Master file and not all rows in the
Master file will have updates. What is the best way to do this? I am
familiar with VLookup. Shall I write aVLookup for each of the 10
columns in each row that need to be replaced so that I can grab the
cell from the new file? Or, is there an easier way?
 
Maybe you could use a macro:

Option Explicit
Sub testme()

Dim MstrWks As Worksheet
Dim UpdWks As Worksheet
Dim MstrKey As Range
Dim UpdKey As Range
Dim UpdCell As Range
Dim res As Variant
Dim DestRow As Long

Set MstrWks = Workbooks("Book1.xls").Worksheets("Sheet1")
Set UpdWks = Workbooks("Book2.xls").Worksheets("sheet1")

With MstrWks
'remove any fill color--you'll be able to
'see the differences
.Cells.Interior.ColorIndex = xlNone
Set MstrKey = .Range("a1", .Cells(.Rows.Count, "A").End(xlUp))
End With

With UpdWks
Set UpdKey = .Range("A1", .Cells(.Rows.Count, "A").End(xlUp))
End With

For Each UpdCell In UpdKey.Cells
res = Application.Match(UpdCell.Value, MstrKey, 0)
If IsError(res) Then
'no match
With MstrWks
DestRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
End With
Else
DestRow = res
End If

'now look at each field
With MstrWks
'are the values to compare in the same columns?
'I assumed they are, but they don't need to be!

'---------------------
'this portion will need to be repeated (or some loop)
'for each pair of fields that should be compared
'including the key field, too!
If .Cells(DestRow, "a").Value _
= UpdWks.Cells(UpdCell.Row, "a").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "a")
.NumberFormat = UpdWks.Cells(UpdCell.Row, "a").NumberFormat
.Value = UpdWks.Cells(UpdCell.Row, "a").Value
.Interior.ColorIndex = 3 'red for me
End With
End If
'- - - - - - - -
If .Cells(DestRow, "b").Value _
= UpdWks.Cells(UpdCell.Row, "b").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "b")
.NumberFormat = UpdWks.Cells(UpdCell.Row, "b").NumberFormat
.Value = UpdWks.Cells(UpdCell.Row, "b").Value
.Interior.ColorIndex = 3 'red for me
End With
End If
'----------------------
End With
Next UpdCell

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)
 
Back
Top