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?

 

[jcl408]

There was a runtime error on that line again.

Take a look at this - you will have a better idea of what I am looking at

So the Name, Account and Brand (D, E and F) are the ones I am looking to match. If you look below you will see the Data 2010 and Data 2009 worksheets - those are the different years data, and then I made a third sheet called "Sheet3" that you cant see there but its indeed there. The other worksheet is something else that isnt relevant to this.

 

[kpark91]

I will be posting a code soon.
I need to first create a template for myself to test on.

 

[jcl408]

Ok I will be awaiting it. Thanks a ton, I had absolutely no idea how I was going to do this

 

[kpark91]

No problem :P

Just make sure you've put this in your "ThisWorkbook" module
and you can change Sheet1, Sheet2, Sheet3 to your sheets.
Sheet1 = 2009
Sheet2 = 2010
Sheet3 = Combined comparison

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("D" & i) = Sheet2.Range("D" & j) And Sheet1.Range("E" & i) = Sheet2.Range("E" & j) And Sheet1.Range("F" & i) = Sheet2.Range("F" & j)) Then
                Sheet3.Range("A" & count & ":" & "I" & count).Value = Sheet1.Range("A" & i & ":" & "I" & i).Value
                Sheet3.Range("J" & count).Value = "2009"
                count = count + 1
                Sheet3.Range("A" & count & ":" & "I" & count).Value = Sheet2.Range("A" & i & ":" & "I" & i).Value
                Sheet3.Range("J" & count).Value = "2010"
                count = count + 1
            End If
        Next j
    Next i
     
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
End Sub

 

[jcl408]

Ok it seems to be working (my Excel is currently frozen haha) so I will let you know when the algorithm finishes. I just went ahead and renamed the sheets to the one in the macro to make it easier, if/when it works I will fool around with the names.

 

[kpark91]

Yea... It takes a very long time and might cause an error because of the sheer number of rows in your worksheets.

the algorithm takes O(n ^ 2) which means it takes maximum time of iterations in maximum number of rows between sheet1 and sheet2 squared..
I'll let you know if I can think of a better algorithm but it will most likely need sorting method.

 

[jcl408]

Yeah there are about 25,000 rows in each worksheet. What kind of machine will I need to do this in a reasonable amount of time? I mean the other option is I could leave my PC on all weekend and let it (hopefully) work through it.

By sorting method what do you mean?

 

[kpark91]

It may or may not work if you do it over the weekends but most likely, not going to work.
I am such a poor programmer but want to get better
so it'd be great if others can pitch in and maybe I can learn from them xD

(If you're reading this, yes, it means you!!!)

And I meant sorting method as in sorting the originals before comparing the three parameters. It should make it a bit faster but I don't know..

 

[jcl408]

I will try this on a beefier machine and see what happens.

If anyone else could help out that would be great too

 

[jcl408]

The algorithm completed but no data was put in Sheet3. The Sheet3 I had was just a blank worksheet.

Is the macro assuming A/B/C is Name, Brand, Account? How do I change this to D/E/F? (D being Name, E being Brand and F being Account?)