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]

Ok so I worked a little and was able to extract the data by name and brand. So now I have two worksheets with only the names and brands I need, the issue now becomes ACCOUNTS.

I need a way to extract ONLY accounts that exist in both the 2009 and 2010 worksheet. Other accounts I don't care about because I am looking to do a direct apples-to-apples comparison.

Honestly I don't really know how I am going to go about this. Any ideas?

 

[jcl408]

No insights on this? Two worksheets, same columns, just need a way to get the ones with the same accounts so I can do an apples-to-apples comparison

 

[taigovinda]

Try setting up a helper column in each of your two worksheets. In that helper column, concatenate the fields that need to match (e.g., A1&B1&C1). Then you can do a vlookup on the concatenated column to assemble the values from the other sheet. If it returns #N/A, you know it's not on the other sheet.

Alternatively, you could add a column to each sheet for "year" that says either 2009 or 2010. Then append one sheet to the bottom of the other and run a pivot table with 2009/2010 field in the columns...

 

[jcl408]

I thought of that but the problem is there are 25,000 rows.

 

[kpark91]

Do you mind a VBA solution (macro)?

And also, are all the same names' data going to be in Worksheet3? If it is, what is its name?

 

[jcl408]

VBAs are fine with me

 

[jcl408]

Do you mind a VBA solution (macro)?

And also, are all the same names' data going to be in Worksheet3? If it is, what is its name?

I will just name it Sheet3 to make it easy.

To elaborate as fully as possible:

There are 9 columns in each of the data worksheets:

A: Region
B: Market
C: Type
D: Name
E: Account
F: Brand
G: Amount
H: Marginal Income
I: Total

What I needed cross-checked at the beginning was D, E and F - but now I made 2 more worksheets (2009 Extraction and 2010 Extraction) that basically matched up the Name and Brand, so what is remaining is making sure it pulls the ones with Accounts (column E) that are in both.

Additionally, if your macro is going to look at Name and Brand anyway I can just delete the Extraction worksheets altogether.

But what I want is a way to compare the Amount, Marginal Income and Total (G, H and I) for each person and their accounts from 2009 to 2010 apples-to-apples.

Let me know if this makes sense

EDIT - this may help:

<TABLE style="WIDTH: 1297pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=1730 border=0><COLGROUP><COL style="WIDTH: 119pt; mso-width-source: userset; mso-width-alt: 5814" span=3 width=159><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: 119pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 28.5pt; BACKGROUND-COLOR: transparent" width=159 height=38>Region</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 119pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=159>Market</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 119pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=159>Type</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 120pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=160>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>West</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">California</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Trade</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Doe, John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT 1</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND 1</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>670</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>West</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">California</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Trade</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Doe, John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT 2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND 2</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>4.72</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>128</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>604.16</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>West</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">California</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Trade</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">Doe, John</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">ACCOUNT 3</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">BRAND 3</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>329</TD><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=right>0</TD></TR></TBODY></TABLE>

So this is what I am looking at (region being Column A, Market being column B, etc. etc.)

I need to extract data by NAME, ACCOUNT and BRAND that appear in both the 2009 and 2010 sheets (called Data 2010 and Data 2009, respectively).

 

[kpark91]

Make sure you make a copy of your workbook when you're trying something new.

Put this in your sheet3 module

Sub FindSame()
 
'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
 
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
 
'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 & ":" & "C" & i).Value = Sheet2.Range("A" & j & ":" & "C" & j).Value) 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

 

[jcl408]

It gave me a runtime error on this line:

If (Sheet1.Range("A" & i & ":" & "C" & i).Value = Sheet2.Range("A" & j & ":" & "C" & j).Value) Then

Edit - hold on a sec and let me add the end sub and then let you know