right tool to compare columns

M

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
113
I have two sets of data: stocks for two different years, 2012 and 2013. If the stock's NAME exists in both years, I want a new column that shows the price difference (year 2013- year 2012).

Since the rows of stock names do not always match, I am not sure what tool to use.

Excel 2012
ABCDEFG
1dateSTOCK 2012PRICE 2012dateSTOCK 2013PRICE 2013
212/31/2012AGILENT TECHNOLOGIES INC3.7412/31/2013AGILENT TECHNOLOGIES INC2.55
312/31/2012ALCOA INC63.4812/31/2013ALCOA INC72.14
412/31/2012ASSET ACCEPTANCE CAPITAL CORP84.9912/31/2013ADVISORSHARES TRUST12.55
512/31/2012ADVISORSHARES TRUST14.1512/31/2013ISHARES TRUST58.05
612/31/2012ISHARES TRUST53.5012/31/2013AMERICAN AIRLINES GROUP INC84.11
712/31/2012ATLANTIC AMERICAN CORP42.1512/31/2013ALTISOURCE ASSET MANAGEMENT CORP3.98
812/31/2012AARONS INC11.0012/31/2013ATLANTIC AMERICAN CORP58.11
912/31/2012AAON INC21.2012/31/2013AARONS INC2.58
1012/31/2012ADVANCE AUTO PARTS INC11.7512/31/2013APPLIED OPTOELECTRONICS INC98.00
1112/31/2012APPLE INC421.4812/31/2013AAON INC12.48
1212/31/2012AMERICAN ASSETS TRUST INC2.0212/31/2013ADVANCE AUTO PARTS INC6.36
1312/31/2012ALMADEN MINERALS LTD16.0812/31/2013APPLE INC452.45
1412/31/2012ADVANTAGE OIL & GAS LTD77.7312/31/2013AMERICAN ASSETS TRUST INC5.44
1512/31/2012ATLAS AIR WORLDWIDE HOLDINGS INC1.54
1612/31/2012ISHARES TRUST61.56
1712/31/2012ALLIANCEBERNSTEIN HOLDING L P115.73
1812/31/2012ABAXIS INC11.44

<tbody>
</tbody>
Sheet1 (2)​
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Sort by date Sort by votes
in h =IFERROR(G2-VLOOKUP(F2,B:C,2,FALSE),"n/a") n/a means stock in 2013 data only

dateSTOCK 2012PRICE 2012dateSTOCK 2013PRICE 2013movement
12/31/2012AGILENT TECHNOLOGIES INC3.74 12/31/2013AGILENT TECHNOLOGIES INC2.55-1.19
12/31/2012ALCOA INC63.48 12/31/2013ALCOA INC72.148.66
12/31/2012ASSET ACCEPTANCE CAPITAL CORP84.99 12/31/2013ADVISORSHARES TRUST12.55-1.6
12/31/2012ADVISORSHARES TRUST14.15 12/31/2013ISHARES TRUST58.054.55
12/31/2012ISHARES TRUST53.5 12/31/2013AMERICAN AIRLINES GROUP INC84.11n/a
12/31/2012ATLANTIC AMERICAN CORP42.15 12/31/2013ALTISOURCE ASSET MANAGEMENT CORP3.98n/a
12/31/2012AARONS INC11 12/31/2013ATLANTIC AMERICAN CORP58.1115.96
12/31/2012AAON INC21.2 12/31/2013AARONS INC2.58-8.42
12/31/2012ADVANCE AUTO PARTS INC11.75 12/31/2013APPLIED OPTOELECTRONICS INC98n/a
12/31/2012APPLE INC421.48 12/31/2013AAON INC12.48-8.72
12/31/2012AMERICAN ASSETS TRUST INC2.02 12/31/2013ADVANCE AUTO PARTS INC6.36-5.39
12/31/2012ALMADEN MINERALS LTD16.08 12/31/2013APPLE INC452.4530.97
12/31/2012ADVANTAGE OIL & GAS LTD77.73 12/31/2013AMERICAN ASSETS TRUST INC5.443.42

<COLGROUP><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 215pt; mso-width-source: userset; mso-width-alt: 10496" width=287><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 2011" width=55><COL style="WIDTH: 27pt; mso-width-source: userset; mso-width-alt: 1316" width=36><COL style="WIDTH: 69pt; mso-width-source: userset; mso-width-alt: 3364" width=92><COL style="WIDTH: 221pt; mso-width-source: userset; mso-width-alt: 10788" width=295><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3730" width=102><COL style="WIDTH: 134pt; mso-width-source: userset; mso-width-alt: 6546" width=179><TBODY>
</TBODY>
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question
Dynamic Table Update by column value
Replies
3
Views
381
Fluff
Fluff
S
  • Question
Extracting Data from another sheet.
Replies
0
Views
497
shahid5788
S
P
  • Question
Extractor
Replies
1
Views
759
Andrew Poulsom
Andrew Poulsom
D
  • Question
Financial Year wise Reports
Replies
1
Views
658
devinali
D
J
  • Question
Help with Formula - Match, Search, etc?
Replies
2
Views
2K
jswinney1
J

Forum statistics

Threads
1,216,116
Messages
6,128,931
Members
449,480
Latest member
yesitisasport

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