Need help: How to compare these two worksheets?

[jcl408]

Hey,

I have a workbook with two worksheets that contain data on accounts. One worksheet is from 2009 and one is from 2010. The setup looks like this:

<TABLE style="WIDTH: 940pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1253 border=0><COLGROUP><COL style="WIDTH: 120pt; mso-width-source: userset; mso-width-alt: 5851" width=160><COL style="WIDTH: 440pt; mso-width-source: userset; mso-width-alt: 21430" width=586><COL style="WIDTH: 164pt; mso-width-source: userset; mso-width-alt: 8009" width=219><COL style="WIDTH: 85pt; mso-width-source: userset; mso-width-alt: 4132" width=113><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 28.5pt; mso-height-source: userset" height=38><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 120pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 28.5pt; BACKGROUND-COLOR: transparent" width=160 height=38>Name</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 440pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=586>Account</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 164pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=219>Brand</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 85pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=113>2010 Amount</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 83pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=111>Marginal Income</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 48pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=64>Total</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Doe, John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT NAME (NY)[NEW YORK CITY]555 BROADWAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND NAME 1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>30</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1108</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>843</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Doe, John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT NAME (NY)[NEW YORK CITY]555 BROADWAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND NAME 2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>22</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>9056</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>1067</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Doe, John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT NAME (NY)[NEW YORK CITY]555 BROADWAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND NAME 3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>15</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>3413</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>900</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Doe, Jane</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT NAME (NJ)[JERSEY CITY]301 UNION WAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND NAME 4</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>0</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>0</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Doe, Jane</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT NAME (NJ)[JERSEY CITY]301 UNION WAY</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND NAME 5</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>44</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>14500</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>483</TD></TR></TBODY></TABLE>

So each worksheet looks like this just the 2009 one has "2009 Amount" and the 2010 one has "2010 Amount"

I want to compare the 2009 and 2010 worksheets but only if they match up for Name, Account and Brand - so if it is John Doe but a different account it wont show up, if it is John Doe and the same account but a different brand it won't show up. But if it finds Doe, John, the same account and the same brand (so the Name, Account and Brand columns all match), then I want it to pull either the 2010 Amount, the Marginal Income or the Total. (I can just put in the column name in the worksheet depending on what I want)

Between 2009 and 2010 obviously there have been different names because some people have left or been added so the worksheets don't perfectly compare. I figure once I pull all the exact matches I can then sort and easily find whatever data I want for my year-to-year comparison.

I have been trying to do this with IF and INDEX statements but I am having a really hard time.

Does anyone have any ideas or can help?

 

[kpark91]

The code I've given you already had D,E,F as Names, Brands, and Accounts.
Trying to figure out why it hadn't worked tho..

 

[jcl408]

I figured it out. The sheets were not actually Sheet1, Sheet2, Sheet3 even though they were named as such but were Sheet4 Sheet5 and Sheet6 because I had used the first three 'sheets' as someting else. So I made a new workbook and just copy and pasted and it worked. Awesome!

 

[kpark91]

ahhhhhh.
Yea... I forgot to mention that. hahah.

I'm glad it worked
I need to think of finding a better way to solve this D:

 

[jcl408]

so the macro only seems to work when I remove columns A-C and just have the DEFGHI (Name, Account, Brand, Cases, Marginal Income, Total). Perhaps the addition of the first three columns is just too much daga for the macro to sort through (just tried it on an Intel i5 with 3.67GB of RAM and it froze). (Oh and I want to emphasize the order is Name-Account-Brand as above you said it has D/E/F as "Names, Brands, and Accounts").

Can we modify the macro to exclude A-C (I will just delete those 3 columns) and act as if A, B and C are "Names, Accounts and Brands" and then DEF are still Cases, Marginal Income and Total? Sorted by name (so column A)?

Then I will go in and write IF formulas to generate the other data and just drag them down

 

[kpark91]

Sorry for the late reply. I was.. sleeping :D

Here is the modified code for you. Still trying to figure out a faster way but I got distracted.. haha;;

Sub FindSame()
 
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    'Sheet1 is 2009 , Sheet2 is 2010
    Dim LR1, LR2, count As Long
    LR1 = Sheet1.Range("A" & Rows.count).End(xlUp).Row
    LR2 = Sheet2.Range("A" & Rows.count).End(xlUp).Row
    count = 2
     
    'Very slow algorithm but oh well...
    For i = 2 To LR1
        For j = 2 To LR2
            'Name, Brand, Account in A,B,C in both sheets
            If (Sheet1.Range("A" & i) = Sheet2.Range("A" & j) And Sheet1.Range("B" & i) = Sheet2.Range("B" & j) And Sheet1.Range("C" & i) = Sheet2.Range("C" & j)) Then
                Sheet3.Range("A" & count & ":" & "F" & count).Value = Sheet1.Range("A" & i & ":" & "F" & i).Value
                Sheet3.Range("G" & count).Value = "2009"
                count = count + 1
                Sheet3.Range("A" & count & ":" & "F" & count).Value = Sheet2.Range("A" & i & ":" & "F" & i).Value
                Sheet3.Range("G" & count).Value = "2010"
                count = count + 1
            End If
        Next j
    Next i
     
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub