VBA to compare worksheets; update certain columns if a match; add new row if not

[Ruthanne]

Thank you in advance for any help!

I would like a macro that can match column A & column B data of Worksheet(WeeklyJob) to column A & column B of Worksheet(Master) then if match is found copy column C through column F into Worksheet(Master) column C through F (and overwrite any [outdated] existing data there may be in those columns [thus updating the job's weekly charges, etc.]).
If match is not found I would like it to copy entire row from Worksheet(WeeklyJobs) into first blank row at end of Worksheet(Master) (thus giving me a new record of a new job from the weekly report).
All the columns in both worksheets are labeled the same (& row 1 is headings).

I am also open to other approaches, of course!

Thank you all for the many times this board has been of great value to me & my co-workers! I am so impressed with all the brilliant minds shining on this board!

 

[Ruthanne]

I should have mentioned that I have been searching archived posts & internet for similar code to copy & tweak and had found a few similar but am overwhelmed at trying to combine them for my double purpose ("overwriting" existing match & creating new record for no match). I also viewed tips for posting so as not to waste others' time but am worried that I haven't properly posted since there hasn't been a reply yet (perhaps it seems as I'm asking for a huge size code with no effort on my part but I have been digging deep!). I don't know how to write code but can usually find code on this board to tweak.

Here is a sample of my data:

Before Macro:
Worksheet(Master)
A B C D E F
Tom Jr 2 3 4 5
Tom Sr 2 3 4 5
Tom 2 3 4 5
Jen 2 3 4 5

Worksheet(WeeklyJob)
A B C D E F
Tom Sr 7 7 7 7
Xav Sr 8 8 8 8


After macro I would like it to perform/look like this:
Worksheet(Master)
A B C D E F
Tom Jr 2 3 4 5
Tom Sr 7 7 7 7
Tom 2 3 4 5
Jen 2 3 4 5
Xav Sr 8 8 8 8

Worksheet(WeeklyJob)
A B C D E F
Tom Sr 7 7 7 7
Xav Sr 8 8 8 8

(I am starting mainly with the code from this thread
http://www.mrexcel.com/forum/showthread.php?t=317992
but I don't understand all the code & how it's affecting the sample data. Also I'm not sure which replies are meant to be code corrections or what code should be replaced.)

Thanks again in advance for any lifeline you can toss!
(By the way, this code is for my boss! Te he! He thinks I know something about Excel...I just know to come running to MrExcel!)
Take care.

 

[Ruthanne]

Bless this Board...I have finally found the following thread & tweaked for half of my intended goal:

Sub compare_move()
'pasted & editted from http://www.mrexcel.com/forum/showthread.php?t=99187

FinalRowSh1 = Worksheets("Master").Range("A65536").End(xlUp).Row
FinalRowSh2 = Worksheets("WeeklyJob").Range("A65536").End(xlUp).Row

For i = FinalRowSh2 To 1 Step -1
For J = FinalRowSh1 To 1 Step -1
If Worksheets("Master").Cells(J, 1) = Worksheets("WeeklyJob").Cells(i, 1) And Worksheets("Master").Cells(J, 1) = Worksheets("WeeklyJob").Cells(i, 1) Then
Worksheets("Master").Cells(J, 3) = Worksheets("WeeklyJob").Cells(i, 3)
Worksheets("Master").Cells(J, 4) = Worksheets("WeeklyJob").Cells(i, 4)
Worksheets("Master").Cells(J, 5) = Worksheets("WeeklyJob").Cells(i, 5)
Worksheets("Master").Cells(J, 6) = Worksheets("WeeklyJob").Cells(i, 6)
End If
Next J
Next i

End Sub

I will be plodding on to figure out how to add the feature "else copy entire row to first blank row of worksheetMaster!...any help is much appreciated...I'm kinda smiling now but was kinda crying after searching two days and getting (pout) no reply. I've even looked at new posts that might be so easy the maybe I could answer in order to try to contribute to the Q&A community and boost my Excel Karma!
Anyway, thanks all for your excellance, kindness & patience with all of us wanna be's!

 

[Ruthanne]

I have tried to piece together pieces for 2nd part of code. I would really be grateful if someone could help correct it (I will insert it into the portion previously posted & successfully running:

Sub compare_move()
'pasted & editted from http://www.mrexcel.com/forum/showthread.php?t=99187

FinalRowSh1 = Worksheets("Master").Range("A65536").End(xlUp).Row
FinalRowSh2 = Worksheets("WeeklyJob").Range("A65536").End(xlUp).Row

For i = FinalRowSh2 To 1 Step -1
For J = FinalRowSh1 To 1 Step -1
If Worksheets("Master").Cells(J, 1) = Worksheets("WeeklyJob").Cells(i, 1) And Worksheets("Master").Cells(J, 1) = Worksheets("WeeklyJob").Cells(i, 1) Then
Worksheets("Master").Cells(J, 3) = Worksheets("WeeklyJob").Cells(i, 3)
Worksheets("Master").Cells(J, 4) = Worksheets("WeeklyJob").Cells(i, 4)
Worksheets("Master").Cells(J, 5) = Worksheets("WeeklyJob").Cells(i, 5)
Worksheets("Master").Cells(J, 6) = Worksheets("WeeklyJob").Cells(i, 6)
Else: Selection.EntireRow.Select
Selection.Copy
FinalRowSh2.Offset(1, 0).Activate
ActiveSheet.Paste

End If
Next J
Next i

End Sub

Please help. Thank you.

 

[Ruthanne]

I tried this but something is still wrong with the last part:

FinalRowSh1 = Worksheets("Master").Range("D65536").End(xlUp).Row
FinalRowSh2 = Worksheets("WeeklyJob").Range("D65536").End(xlUp).Row

For i = FinalRowSh2 To 1 Step -1
For J = FinalRowSh1 To 1 Step -1
If Worksheets("Master").Cells(J, 4) = Worksheets("WeeklyJob").Cells(i, 4) And Worksheets("Master").Cells(J, 5) = Worksheets("WeeklyJob").Cells(i, 5) Then
Worksheets("Master").Cells(J, 10) = Worksheets("WeeklyJob").Cells(i, 10)
Worksheets("Master").Cells(J, 11) = Worksheets("WeeklyJob").Cells(i, 11)
Worksheets("Master").Cells(J, 12) = Worksheets("WeeklyJob").Cells(i, 12)
Worksheets("Master").Cells(J, 13) = Worksheets("WeeklyJob").Cells(i, 13)
Worksheets("Master").Cells(J, 14) = Worksheets("WeeklyJob").Cells(i, 14)
Else: FinalRowSh2.EntireRow.Copy Destination:=FinalRowSh1(myRow + 1, 1)
End If
Next J
Next i

End Sub

I'll keep trying. I just can't seem to find the correct mix & max code style with intended outcomes! Aargh & tears!

 

[Ruthanne]

Does anyone know if I'm on the right track?
Here is a different approach for the last part but it doesn't work:

FinalRowSh1 = Worksheets("Master").Range("D65536").End(xlUp).Row
FinalRowSh2 = Worksheets("WeeklyJob").Range("D65536").End(xlUp).Row

For i = FinalRowSh2 To 1 Step -1
For J = FinalRowSh1 To 1 Step -1
If Worksheets("Master").Cells(J, 4) = Worksheets("WeeklyJob").Cells(i, 4) And Worksheets("Master").Cells(J, 5) = Worksheets("WeeklyJob").Cells(i, 5) Then
Worksheets("Master").Cells(J, 10) = Worksheets("WeeklyJob").Cells(i, 10)
Worksheets("Master").Cells(J, 11) = Worksheets("WeeklyJob").Cells(i, 11)
Worksheets("Master").Cells(J, 12) = Worksheets("WeeklyJob").Cells(i, 12)
Worksheets("Master").Cells(J, 13) = Worksheets("WeeklyJob").Cells(i, 13)
Worksheets("Master").Cells(J, 14) = Worksheets("WeeklyJob").Cells(i, 14)
Else: FinalRowSh2.EntireRow.Copy FinalRowSh1.Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End If
Next J
Next i

End Sub

Thank you in advance for any help on the last portion intended to copy entire row to first blank row of Weekly Job worksheet.

 

[jindon]

try

Sub test()
Dim a, i As Long, ii As Integer, z As String
a = Sheet("WeeklyJob").Range("a1").CurrentRegion.Resize(,6).Value
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a,1)
        z = a(i,1) & ";" & a(i,2)
        If Not .exists(z) Then
            .add z, Array(a(i,1), a(i,2), a(i,3), a(i,4), a(i,5), a(i,6))
        End If
    Next
    a = Sheets("Master").Range("a1").CurrentRegion.Resize(,6).Value
    For i = 1 To UBound(a,1)
        z = a(i,1) & ";" & a(i,2)
        If .exists(z) Then
            w = .item(z)
            For ii = 3 To 6 : a(i,ii) = w(ii - 1) : Next
            .remove z
        End If
    Next
    If .count > 0 Then
        Sheets("Master").Range("a" & Rows.Count).End(xlUp)(2) _
        .Resize(.count, 6).Value = Application.Transpose(Application.Transpose(.items))
    End If
End With
End Sub

 

[Ruthanne]

Bless your heart Jindon-san...angel of mercy!
Your code works perfect for the 2nd situation of writing the new records to the last row...but doesn't seem to do the first part for some reason. In my test sample the code is not changing Tom Sr's line to all 7's....hmmm.
Your code looks amazing & complex...and like maybe a new generation of language? Looks very hi-tech!
I was a little panicked because my posted sample is somewhat different from my real sample in that it has less columns in different location but I think I can edit the numbers in the code....yikes. However for running your code I am using the exact posted sample & columns.
Also if possible for my real sample I would like to paste entire row (vice specific columns) if the "else" condition exists since I think that will be more comprehensive for future use...unless that is not recommended for speed or programming reasons/logic.

Thanks sooooo much for your thousands of posts & helping mine! I will keep trying to get the 1st part of the new code to work.
There was apparently a little typo where "Sheet" should have been "Sheets" & bugged out...am I showing how green I am at this?
Sorry I didn't get to thank you last night as I had gone home for the evening but a million thanks today (I have my international clock at my cube & it says 2:36 am in Tokyo!)
My heartfelt condolences for the people & families who suffered at Akihabara.
Sincerely,
Ruth

long overdue p.s. I am using Excel 2003

 

[jindon]

I just wrote the code from your first post. I haven't read all through the thread...

Can you explain what you want to do ?

 

[Ruthanne]

Thank you so much for helping me Jindon.
The following is my real situation & what I would like the code to do:
Check column D & column E data of Worksheet(WeeklyJob) for a match to column D & column E of Worksheet(Master); then if match is found copy column A, B & F through P into Worksheet(Master) A, B & F through P (and overwrite any [outdated] existing data there may be in those columns [thus updating the job's weekly charges/changes, etc.]).
If match is not found I would like it to copy entire row from Worksheet(WeeklyJobs) into first blank row at end of Worksheet(Master) (thus giving me a new record of a new job from the weekly report).
All the columns in both worksheets are labeled the same (& row 1 is headings).

Many thanks again & kind regards!
Ruth