Compare two worksheets, update specific columns, or add new row

blackbeginnings

New Member
Joined
Nov 5, 2013
Messages
1
Because I am a novice to VBA and learning from posts found on this forum, I am trying to understand some VBA code that I found on this site (many thx jindon) that comes very close to what I am needing. I have attached a sample worksheet that illustrates the before and after.

What I am trying to accomplish is two-fold. I have a spreadsheet with 14 columns on the Customer worksheet of raw data (see attachment of sample data). What I would like to accomplish is to have a macro that can be ran during our first quarter that covers the months of Oct – Dec that will copy the data in columns A, D, E, M, F, K, and N located on the Customer Sheet and place that information in the respective columns A, B, C, D, E, L, and M on the Updated worksheet that has 13 columns. I think that’s the easy part for me to understand.


What I am having trouble with is developing a macro that I can run in the second quarter that will perform a search on the company name (column B on Updated worksheet) and compare that to the company name on the Customer worksheet (column D). If a duplicate record is found, then I need it to take the information found in columns M, F, K, and N on the Customer worksheet and update columns F, G, L, and M on the Update worksheet. If the comparison does not find a match, then add a new record on the next available blank row on the Updated worksheet and copy columns A, D, E, M, F, K, and N located on the Customer Sheet and place that information in the respective columns A, B, C, D, E, L, and M on the Updated worksheet.

This process will need to be repeated for each of the remaining quarters. The only difference is if the search finds duplicate company names, the information located in columns M, F, K, and N on the Customer worksheet will be placed in columns H, I, L, M for Qtr 3 and J, K, L, and M for Qtr 4 on the Updated worksheet.



Below is the code I referenced above that comes very close to what I am needing but it does not update the specific columns as outlined. However, it does add the new record if a duplicate is not found. Any assistance and/or guidance is greatly appreciated from one and all. Because I am such a novice, it would also be helpful to me in my learning process if comments explaining what the code is doing are included. Thank you in advance.

Code:
Dim a, i As Long, ii As Integer, z As String
Dim n As Long, AB(), F_P(), x As Long, e
a = Sheet("WeeklyJob").Range("a1").CurrentRegion.Resize(,16).Value
ReDim AB(1 To UBound(a,1), 1 To 2)
ReDim F_P(1 To UBound(a,1), 1 To 11)
With CreateObject("Scripting.Dictionary")
    .CompareMode = vbTextCompare
    For i = 1 To UBound(a,1)
        z = a(i,4) & ";" & a(i,5)
        If Not .exists(z) Then
            n = n + 1
            For ii = 1 To 13 
                If ii < 3 Then
                    AB(n,ii) = a(i,ii)
                Else
                    F_P(n, ii - 2) = a(i, ii + 3)
                End If
            .add z, n
        End If
    Next
    a = Sheets("Master").Range("a1").CurrentRegion.Resize(,16).Value
    For i = 1 To UBound(a,1)
        z = a(i,4) & ";" & a(i,5)
        If .exists(z) Then
            x = .item(z)
            For ii = 1 To 13
                If ii < 3 Then
                    a(i,ii) = AB(x, ii)
                Else
                    a(i, ii + 3) = F_P(x, ii - 2)
                End If
            Next
            .remove z
        End If
    Next
    Sheets("Master").Range("a1").CurrentRegion.Resize(,16).Value = a
    If .count > 0 Then
        ReDim a(1 To .Count, 1 To 16) : n = 0
        For Each e In .keys
            x = .item(e) : n = n + 1
            For ii = 1 To 13
                If ii < 3 Then
                    a(n, ii) = AB(x,ii)
                Else
                    a(n, ii + 3) = F_P(x, ii - 2)
                End If
            Next
        Next
        Sheets("Master").Range("a" & Rows.Count).End(xlUp)(2) _
        .Resize(n, 16).Value = a
    End If
End With


CUSTOMER DATA
ID #FNameLNameCompanyRegion# of Empl# of HrsCurrentProcessedReturnsAs of DateInitial LevelCurrent LevelQtr
1JaneDoeXYZ SalesMidwest2130Yes151310/1/2013LowLow1
2DavidSmithDirect BizMid-Atlantic52100Yes49610/15/2014LowLow1
3DavidJonesExpressNortheast2316No23311/3/2013Low-MediumLow-Medium1
4MikeBlackXYZ SalesPacific3131No24812/18/2013MediumMedium1
5MaryMooreABC BizSoutheast2012Yes211512/5/2013LowMedium1
6MichelleJonesExpressNortheast4751Yes361312/19/2013MediumMedium1
How the above data table looks in Quarter 1
ID #FNameLNameCompanyRegion# of Empl# of HrsCurrentProcessedReturnsAs of DateInitial LevelCurrent LevelQtr
1JaneDoeXYZ SalesMidwest2130Yes151310/1/2013LowLow1
2DavidSmithDirect BizMid-Atlantic52100Yes4961/15/2014LowLow-Medium2
3DavidJonesExpressNortheast2316No23311/3/2013Low-MediumMedium-High1
4MichelleJonesExpressNortheast4751Yes36133/6/2014MediumMedium-High2
5MikeBlackXYZ SalesPacific3163Yes4421/8/2014MediumHigh2
6MaryMooreABC BizSoutheast2010Yes20313/27/2014LowMedium-High2
7SamWrightRight CoSouthwest1010Yes10013/13/2014LowLow-Medium2
How the above data table looks in Quarter 2

<tbody>
</tbody>

UPDATED DATA
Qtr 1Qtr 2Qtr 3Qtr 4Optional Fields
ID #CompanyRegionCurrent Level# of EmpCurrent Level# of EmpCurrent Level# of EmpCurrent Level# of EmpAs of DateQtrFNameLName
1XYZ SalesMidwestLow2110/1/20131JaneDoe
2Direct BizMid-AtlanticLow5210/15/20141DavidSmith
3ExpressNortheastLow-Medium2311/3/20131DavidJones
4XYZ SalesPacificMedium3112/18/20131MikeBlack
5ABC BizSoutheastMedium2012/5/20131MaryMoore
6ExpressNortheastMedium4712/19/20131MichelleJones
This is how the above table should look after update in Quarter 1
Qtr 1Qtr 2Qtr 3Qtr 4Optional Fields
ID #CompanyRegionCurrent Level# of EmpCurrent Level# of EmpCurrent Level# of EmpCurrent Level# of EmpAs of DateQtrFNameLName
1XYZ SalesMidwestLow21Low2110/1/20131JaneDoe
2Direct BizMid-AtlanticLow-Medium52Low-Medium521/15/20142DavidSmith
3ExpressNortheastMedium-High23Medium-High2311/3/20131DavidJones
4XYZ SalesPacificMedium31High311/8/20142MikeBlack
5ABC BizSoutheastMedium20Medium-High203/27/20141MaryMoore
6ExpressNortheastMedium-High47Medium-High473/6/20142MichelleJones
7Right CoSouthwestLow-Medium103/13/20142SamWright
This is how the above table should look after update in Quarter 2

<tbody>
</tbody>




 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Watch MrExcel Video

Forum statistics

Threads
1,127,199
Messages
5,623,321
Members
415,966
Latest member
ctorohuamanchumo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top