Match,Update & insert new data - Please HELP!

[TK]

I have a master.csv (source) and myrpt.xls (destination)file, I need to match
the acct#.
1) if acct# match update the amount (total column)
2) if acct# don't match insert new data (Account, State & Total) from master
to myrpt to next blank row.

Master.csv
A B D E F
ACCOUNT State PAST_60 PAST_90 TOTAL
11-12792 CA 225.15 886.44 1,111.59
11-25851 ME 591.94 130.80 722.74
11-32239 AZ - 2,676.00 2,676.00
11-34849 AR 1,350.00 900.00 2,250.00
11-35239 ID 135.67 134.42 270.09
11-40015 CO 106.75 353.74 460.49


myrpt.xls (sheet1)
A B C
ACCOUNT State TOTAL
11-25851 ME 650.74
11-35239 ID 150.00

Thank you in advance.

 

[Dave Peterson]

I saved this from a similar post.

I assumed that Column E was the total line (it looked like column 5).

Open both files.

Change the names to match on these lines to match the correct sheet names:

Set MstrWks = Workbooks("master.csv").Worksheets(1)
Set UpdWks = Workbooks("myrpt.xls").Worksheets("sheet999")

Save the .csv file as a new name--just in case it's wrong. The original worked
fine, but I didn't test it with your changes.

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("master.csv").Worksheets(1)
Set UpdWks = Workbooks("myrpt.xls").Worksheets("sheet999")

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
If .Cells(DestRow, "a").Value _
= UpdWks.Cells(UpdCell.Row, "a").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "a")
.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
'- - - - - - - -
'column E compared with column C, right????
'(all of the if/then/else needs to be fixed if it's wrong)
If .Cells(DestRow, "e").Value _
= UpdWks.Cells(UpdCell.Row, "c").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "E")
.Value = UpdWks.Cells(UpdCell.Row, "C").Value
.Interior.ColorIndex = 3 'red for me
End With
End If
'----------------------
End With
Next UpdCell
End Sub

 

[TK]

Thanks a million Dave - I just had to few changes & it worked perfectly. You
are the best !

 

[TK]

Thank you Dave, you rock !

I saved this from a similar post.

I assumed that Column E was the total line (it looked like column 5).

Open both files.

Change the names to match on these lines to match the correct sheet names:

Set MstrWks = Workbooks("master.csv").Worksheets(1)
Set UpdWks = Workbooks("myrpt.xls").Worksheets("sheet999")

Save the .csv file as a new name--just in case it's wrong. The original worked
fine, but I didn't test it with your changes.

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("master.csv").Worksheets(1)
Set UpdWks = Workbooks("myrpt.xls").Worksheets("sheet999")

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
If .Cells(DestRow, "a").Value _
= UpdWks.Cells(UpdCell.Row, "a").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "a")
.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
'- - - - - - - -
'column E compared with column C, right????
'(all of the if/then/else needs to be fixed if it's wrong)
If .Cells(DestRow, "e").Value _
= UpdWks.Cells(UpdCell.Row, "c").Value Then
'no change, do nothing
Else
'changed!
With .Cells(DestRow, "E")
.Value = UpdWks.Cells(UpdCell.Row, "C").Value
.Interior.ColorIndex = 3 'red for me
End With
End If
'----------------------
End With
Next UpdCell
End Sub

 

[Dave Peterson]

Glad it worked ok.

Thank you Dave, you rock !