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

B

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


[TABLE="width: 798"]
<tbody>[TR]
[TD="width: 1063, bgcolor: transparent, colspan: 14"]CUSTOMER DATA[/TD]
[/TR]
[TR]
[/TR]
[TR]
[TD="bgcolor: yellow"]ID #[/TD]
[TD="bgcolor: yellow"]FName[/TD]
[TD="bgcolor: yellow"]LName[/TD]
[TD="bgcolor: yellow"]Company[/TD]
[TD="bgcolor: yellow"]Region[/TD]
[TD="bgcolor: yellow"]# of Empl[/TD]
[TD="bgcolor: yellow"]# of Hrs[/TD]
[TD="bgcolor: yellow"]Current[/TD]
[TD="bgcolor: yellow"]Processed[/TD]
[TD="bgcolor: yellow"]Returns[/TD]
[TD="bgcolor: yellow"]As of Date[/TD]
[TD="bgcolor: yellow"]Initial Level[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]Qtr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]Jane[/TD]
[TD="bgcolor: transparent"]Doe[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Midwest[/TD]
[TD="bgcolor: transparent"]21[/TD]
[TD="bgcolor: transparent"]30[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent, align: right"]10/1/2013[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]David[/TD]
[TD="bgcolor: transparent"]Smith[/TD]
[TD="bgcolor: transparent"]Direct Biz[/TD]
[TD="bgcolor: transparent"]Mid-Atlantic[/TD]
[TD="bgcolor: transparent"]52[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]49[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent, align: right"]10/15/2014[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]David[/TD]
[TD="bgcolor: transparent"]Jones[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: transparent"]23[/TD]
[TD="bgcolor: transparent"]16[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]23[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent, align: right"]11/3/2013[/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Pacific[/TD]
[TD="bgcolor: transparent"]31[/TD]
[TD="bgcolor: transparent"]31[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]24[/TD]
[TD="bgcolor: transparent"]8[/TD]
[TD="bgcolor: transparent, align: right"]12/18/2013[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Mary[/TD]
[TD="bgcolor: transparent"]Moore[/TD]
[TD="bgcolor: transparent"]ABC Biz[/TD]
[TD="bgcolor: transparent"]Southeast[/TD]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]12[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]21[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent, align: right"]12/5/2013[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Michelle[/TD]
[TD="bgcolor: transparent"]Jones[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: transparent"]47[/TD]
[TD="bgcolor: transparent"]51[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]36[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent, align: right"]12/19/2013[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 13"]How the above data table looks in Quarter 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]ID #[/TD]
[TD="bgcolor: yellow"]FName[/TD]
[TD="bgcolor: yellow"]LName[/TD]
[TD="bgcolor: yellow"]Company[/TD]
[TD="bgcolor: yellow"]Region[/TD]
[TD="bgcolor: yellow"]# of Empl[/TD]
[TD="bgcolor: yellow"]# of Hrs[/TD]
[TD="bgcolor: yellow"]Current[/TD]
[TD="bgcolor: yellow"]Processed[/TD]
[TD="bgcolor: yellow"]Returns[/TD]
[TD="bgcolor: yellow"]As of Date[/TD]
[TD="bgcolor: yellow"]Initial Level[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]Qtr[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]Jane[/TD]
[TD="bgcolor: transparent"]Doe[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Midwest[/TD]
[TD="bgcolor: transparent"]21[/TD]
[TD="bgcolor: transparent"]30[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]15[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent, align: right"]10/1/2013[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]David[/TD]
[TD="bgcolor: transparent"]Smith[/TD]
[TD="bgcolor: transparent"]Direct Biz[/TD]
[TD="bgcolor: transparent"]Mid-Atlantic[/TD]
[TD="bgcolor: transparent"]52[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]49[/TD]
[TD="bgcolor: transparent"]6[/TD]
[TD="bgcolor: transparent, align: right"]1/15/2014[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]David[/TD]
[TD="bgcolor: transparent"]Jones[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: transparent"]23[/TD]
[TD="bgcolor: transparent"]16[/TD]
[TD="bgcolor: transparent"]No[/TD]
[TD="bgcolor: transparent"]23[/TD]
[TD="bgcolor: transparent"]3[/TD]
[TD="bgcolor: transparent, align: right"]11/3/2013[/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]Medium-High[/TD]
[TD="bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]Michelle[/TD]
[TD="bgcolor: transparent"]Jones[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: transparent"]47[/TD]
[TD="bgcolor: transparent"]51[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]36[/TD]
[TD="bgcolor: transparent"]13[/TD]
[TD="bgcolor: transparent, align: right"]3/6/2014[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]Medium-High[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]Mike[/TD]
[TD="bgcolor: transparent"]Black[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Pacific[/TD]
[TD="bgcolor: transparent"]31[/TD]
[TD="bgcolor: transparent"]63[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]44[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: transparent, align: right"]1/8/2014[/TD]
[TD="bgcolor: transparent"]Medium[/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Mary[/TD]
[TD="bgcolor: transparent"]Moore[/TD]
[TD="bgcolor: transparent"]ABC Biz[/TD]
[TD="bgcolor: transparent"]Southeast[/TD]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]203[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent, align: right"]3/27/2014[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Medium-High[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]Sam[/TD]
[TD="bgcolor: transparent"]Wright[/TD]
[TD="bgcolor: transparent"]Right Co[/TD]
[TD="bgcolor: transparent"]Southwest[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: transparent"]Yes[/TD]
[TD="bgcolor: transparent"]100[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: transparent, align: right"]3/13/2014[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 13"]How the above data table looks in Quarter 2[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 919"]
<tbody>[TR]
[TD="width: 1224, bgcolor: transparent, colspan: 15"]UPDATED DATA[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA, colspan: 2"]Qtr 1[/TD]
[TD="bgcolor: transparent, colspan: 2"]Qtr 2[/TD]
[TD="bgcolor: #E2EFDA, colspan: 2"]Qtr 3[/TD]
[TD="bgcolor: transparent, colspan: 2"]Qtr 4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #FFC000, colspan: 2"]Optional Fields[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]ID #[/TD]
[TD="bgcolor: yellow"]Company[/TD]
[TD="bgcolor: yellow"]Region[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]As of Date[/TD]
[TD="bgcolor: yellow"]Qtr[/TD]
[TD="bgcolor: yellow"]FName[/TD]
[TD="bgcolor: yellow"]LName[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Midwest[/TD]
[TD="bgcolor: #E2EFDA"]Low[/TD]
[TD="bgcolor: #E2EFDA"]21[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10/1/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]Jane[/TD]
[TD="bgcolor: #FFC000"]Doe[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]Direct Biz[/TD]
[TD="bgcolor: transparent"]Mid-Atlantic[/TD]
[TD="bgcolor: #E2EFDA"]Low[/TD]
[TD="bgcolor: #E2EFDA"]52[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10/15/2014[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]David[/TD]
[TD="bgcolor: #FFC000"]Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: #E2EFDA"]Low-Medium[/TD]
[TD="bgcolor: #E2EFDA"]23[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11/3/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]David[/TD]
[TD="bgcolor: #FFC000"]Jones[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Pacific[/TD]
[TD="bgcolor: #E2EFDA"]Medium[/TD]
[TD="bgcolor: #E2EFDA"]31[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12/18/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]Mike[/TD]
[TD="bgcolor: #FFC000"]Black[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]ABC Biz[/TD]
[TD="bgcolor: transparent"]Southeast[/TD]
[TD="bgcolor: #E2EFDA"]Medium[/TD]
[TD="bgcolor: #E2EFDA"]20[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12/5/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]Mary[/TD]
[TD="bgcolor: #FFC000"]Moore[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: #E2EFDA"]Medium[/TD]
[TD="bgcolor: #E2EFDA"]47[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12/19/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]Michelle[/TD]
[TD="bgcolor: #FFC000"]Jones[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 11"]This is how the above table should look after update in Quarter 1[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #E2EFDA, colspan: 2"]Qtr 1[/TD]
[TD="bgcolor: transparent, colspan: 2"]Qtr 2[/TD]
[TD="bgcolor: #E2EFDA, colspan: 2"]Qtr 3[/TD]
[TD="bgcolor: transparent, colspan: 2"]Qtr 4[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: #FFC000, colspan: 2"]Optional Fields[/TD]
[/TR]
[TR]
[TD="bgcolor: yellow"]ID #[/TD]
[TD="bgcolor: yellow"]Company[/TD]
[TD="bgcolor: yellow"]Region[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]Current Level[/TD]
[TD="bgcolor: yellow"]# of Emp[/TD]
[TD="bgcolor: yellow"]As of Date[/TD]
[TD="bgcolor: yellow"]Qtr[/TD]
[TD="bgcolor: yellow"]FName[/TD]
[TD="bgcolor: yellow"]LName[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Midwest[/TD]
[TD="bgcolor: #E2EFDA"]Low[/TD]
[TD="bgcolor: #E2EFDA"]21[/TD]
[TD="bgcolor: transparent"]Low[/TD]
[TD="bgcolor: transparent"]21[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]10/1/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]Jane[/TD]
[TD="bgcolor: #FFC000"]Doe[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"]Direct Biz[/TD]
[TD="bgcolor: transparent"]Mid-Atlantic[/TD]
[TD="bgcolor: #E2EFDA"]Low-Medium[/TD]
[TD="bgcolor: #E2EFDA"]52[/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]52[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/15/2014[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: #FFC000"]David[/TD]
[TD="bgcolor: #FFC000"]Smith[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]3[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: #E2EFDA"]Medium-High[/TD]
[TD="bgcolor: #E2EFDA"]23[/TD]
[TD="bgcolor: transparent"]Medium-High[/TD]
[TD="bgcolor: transparent"]23[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]11/3/2013[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]David[/TD]
[TD="bgcolor: #FFC000"]Jones[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]4[/TD]
[TD="bgcolor: transparent"]XYZ Sales[/TD]
[TD="bgcolor: transparent"]Pacific[/TD]
[TD="bgcolor: #E2EFDA"]Medium[/TD]
[TD="bgcolor: #E2EFDA"]31[/TD]
[TD="bgcolor: transparent"]High[/TD]
[TD="bgcolor: transparent"]31[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1/8/2014[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: #FFC000"]Mike[/TD]
[TD="bgcolor: #FFC000"]Black[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"]ABC Biz[/TD]
[TD="bgcolor: transparent"]Southeast[/TD]
[TD="bgcolor: #E2EFDA"]Medium[/TD]
[TD="bgcolor: #E2EFDA"]20[/TD]
[TD="bgcolor: transparent"]Medium-High[/TD]
[TD="bgcolor: transparent"]20[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/27/2014[/TD]
[TD="bgcolor: transparent"]1[/TD]
[TD="bgcolor: #FFC000"]Mary[/TD]
[TD="bgcolor: #FFC000"]Moore[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent"]Express[/TD]
[TD="bgcolor: transparent"]Northeast[/TD]
[TD="bgcolor: #E2EFDA"]Medium-High[/TD]
[TD="bgcolor: #E2EFDA"]47[/TD]
[TD="bgcolor: transparent"]Medium-High[/TD]
[TD="bgcolor: transparent"]47[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/6/2014[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: #FFC000"]Michelle[/TD]
[TD="bgcolor: #FFC000"]Jones[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"]Right Co[/TD]
[TD="bgcolor: transparent"]Southwest[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"]Low-Medium[/TD]
[TD="bgcolor: transparent"]10[/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: #E2EFDA"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]3/13/2014[/TD]
[TD="bgcolor: transparent"]2[/TD]
[TD="bgcolor: #FFC000"]Sam[/TD]
[TD="bgcolor: #FFC000"]Wright[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, colspan: 12"]This is how the above table should look after update in Quarter 2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]




 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
You must log in or register to reply here.

Similar threads

BigPaws
  • Question Question
VBA to create new worksheets from a template, populate the new worksheet by copying data from another sheet to specific locations.
Replies
0
Views
192
BigPaws
BigPaws
K
  • Question Question
Modifying Existing VBA Code to add 2 additional data columns
Replies
0
Views
318
keranali
K
S
  • Solved
How to separate text out to a new column - Text to Column does not work
Replies
7
Views
690
kevin9999
K
1
  • Question Question
Auto filter column on vlookup update on every sheets of the workbook
Replies
1
Views
317
10ml
1
J
  • Question Question
Move Table Row to Another Sheet
Replies
6
Views
659
jessebh2003
J

Forum statistics

Threads
1,223,239
Messages
6,170,947
Members
452,368
Latest member
jayp2104

Share this page

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
Back
Top