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
dateSTOCK 2012PRICE 2012dateSTOCK 2013PRICE 2013
AGILENT TECHNOLOGIES INC12/31/2013AGILENT TECHNOLOGIES INC
ALCOA INC12/31/2013ALCOA INC
ASSET ACCEPTANCE CAPITAL CORP12/31/2013ADVISORSHARES TRUST
ADVISORSHARES TRUST12/31/2013ISHARES TRUST
ISHARES TRUST12/31/2013AMERICAN AIRLINES GROUP INC
ATLANTIC AMERICAN CORP12/31/2013ALTISOURCE ASSET MANAGEMENT CORP
AARONS INC12/31/2013ATLANTIC AMERICAN CORP
AAON INC12/31/2013AARONS INC
ADVANCE AUTO PARTS INC12/31/2013APPLIED OPTOELECTRONICS INC
APPLE INC12/31/2013AAON INC
AMERICAN ASSETS TRUST INC12/31/2013ADVANCE AUTO PARTS INC
ALMADEN MINERALS LTD12/31/2013APPLE INC
ADVANTAGE OIL & GAS LTD12/31/2013AMERICAN ASSETS TRUST INC
ATLAS AIR WORLDWIDE HOLDINGS INC
ISHARES TRUST
ALLIANCEBERNSTEIN HOLDING L P
ABAXIS INC

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]3.74[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.55[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]63.48[/TD]
[TD="align: right"][/TD]

[TD="align: right"]72.14[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]84.99[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12.55[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]14.15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]58.05[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]53.50[/TD]
[TD="align: right"][/TD]

[TD="align: right"]84.11[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]42.15[/TD]
[TD="align: right"][/TD]

[TD="align: right"]3.98[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]11.00[/TD]
[TD="align: right"][/TD]

[TD="align: right"]58.11[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]21.20[/TD]
[TD="align: right"][/TD]

[TD="align: right"]2.58[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]11.75[/TD]
[TD="align: right"][/TD]

[TD="align: right"]98.00[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]421.48[/TD]
[TD="align: right"][/TD]

[TD="align: right"]12.48[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]2.02[/TD]
[TD="align: right"][/TD]

[TD="align: right"]6.36[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]16.08[/TD]
[TD="align: right"][/TD]

[TD="align: right"]452.45[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]77.73[/TD]
[TD="align: right"][/TD]

[TD="align: right"]5.44[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]1.54[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]61.56[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]115.73[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]18[/TD]
[TD="align: right"]12/31/2012[/TD]

[TD="align: right"]11.44[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>
Sheet1 (2)​
 
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

[TABLE="width: 853"]
<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>[TR]
[TD="class: xl63, width: 92, bgcolor: white"]date[/TD]
[TD="class: xl63, width: 287, bgcolor: white"]STOCK 2012[/TD]
[TD="class: xl63, width: 91, bgcolor: white, colspan: 2"]PRICE 2012[/TD]
[TD="class: xl63, width: 92, bgcolor: white"]date[/TD]
[TD="class: xl63, width: 295, bgcolor: white"]STOCK 2013[/TD]
[TD="class: xl63, width: 102, bgcolor: white"]PRICE 2013[/TD]
[TD="class: xl65, width: 179, bgcolor: white"]movement[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]AGILENT TECHNOLOGIES INC[/TD]
[TD="class: xl64, bgcolor: white"]3.74[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]AGILENT TECHNOLOGIES INC[/TD]
[TD="class: xl64, bgcolor: white"]2.55[/TD]
[TD="bgcolor: transparent, align: right"]-1.19[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ALCOA INC[/TD]
[TD="class: xl64, bgcolor: white"]63.48[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]ALCOA INC[/TD]
[TD="class: xl64, bgcolor: white"]72.14[/TD]
[TD="bgcolor: transparent, align: right"]8.66[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ASSET ACCEPTANCE CAPITAL CORP[/TD]
[TD="class: xl64, bgcolor: white"]84.99[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]ADVISORSHARES TRUST[/TD]
[TD="class: xl64, bgcolor: white"]12.55[/TD]
[TD="bgcolor: transparent, align: right"]-1.6[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ADVISORSHARES TRUST[/TD]
[TD="class: xl64, bgcolor: white"]14.15[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]ISHARES TRUST[/TD]
[TD="class: xl64, bgcolor: white"]58.05[/TD]
[TD="bgcolor: transparent, align: right"]4.55[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ISHARES TRUST[/TD]
[TD="class: xl64, bgcolor: white"]53.5[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]AMERICAN AIRLINES GROUP INC[/TD]
[TD="class: xl64, bgcolor: white"]84.11[/TD]
[TD="bgcolor: transparent"]n/a[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ATLANTIC AMERICAN CORP[/TD]
[TD="class: xl64, bgcolor: white"]42.15[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]ALTISOURCE ASSET MANAGEMENT CORP[/TD]
[TD="class: xl64, bgcolor: white"]3.98[/TD]
[TD="bgcolor: transparent"]n/a[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]AARONS INC[/TD]
[TD="class: xl64, bgcolor: white"]11[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]ATLANTIC AMERICAN CORP[/TD]
[TD="class: xl64, bgcolor: white"]58.11[/TD]
[TD="bgcolor: transparent, align: right"]15.96[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]AAON INC[/TD]
[TD="class: xl64, bgcolor: white"]21.2[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]AARONS INC[/TD]
[TD="class: xl64, bgcolor: white"]2.58[/TD]
[TD="bgcolor: transparent, align: right"]-8.42[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ADVANCE AUTO PARTS INC[/TD]
[TD="class: xl64, bgcolor: white"]11.75[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]APPLIED OPTOELECTRONICS INC[/TD]
[TD="class: xl64, bgcolor: white"]98[/TD]
[TD="bgcolor: transparent"]n/a[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]APPLE INC[/TD]
[TD="class: xl64, bgcolor: white"]421.48[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]AAON INC[/TD]
[TD="class: xl64, bgcolor: white"]12.48[/TD]
[TD="bgcolor: transparent, align: right"]-8.72[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]AMERICAN ASSETS TRUST INC[/TD]
[TD="class: xl64, bgcolor: white"]2.02[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]ADVANCE AUTO PARTS INC[/TD]
[TD="class: xl64, bgcolor: white"]6.36[/TD]
[TD="bgcolor: transparent, align: right"]-5.39[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ALMADEN MINERALS LTD[/TD]
[TD="class: xl64, bgcolor: white"]16.08[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]APPLE INC[/TD]
[TD="class: xl64, bgcolor: white"]452.45[/TD]
[TD="bgcolor: transparent, align: right"]30.97[/TD]
[/TR]
[TR]
[TD="class: xl64, bgcolor: white"]12/31/2012[/TD]
[TD="class: xl63, bgcolor: white"]ADVANTAGE OIL & GAS LTD[/TD]
[TD="class: xl64, bgcolor: white"]77.73[/TD]
[TD="class: xl64, bgcolor: white"] [/TD]
[TD="class: xl63, bgcolor: white"]12/31/2013[/TD]
[TD="class: xl63, bgcolor: white"]AMERICAN ASSETS TRUST INC[/TD]
[TD="class: xl64, bgcolor: white"]5.44[/TD]
[TD="bgcolor: transparent, align: right"]3.42[/TD]
[/TR]
</TBODY>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Solved
Lookup based on multiple criteria
2 3
Replies
22
Views
1K
paulkc
P
S
  • Question Question
Dynamic Table Update by column value
Replies
3
Views
471
Fluff
Fluff
S
  • Question Question
Extracting Data from another sheet.
Replies
0
Views
584
shahid5788
S
P
  • Question Question
Extractor
Replies
1
Views
889
Andrew Poulsom
Andrew Poulsom
D
  • Question Question
Financial Year wise Reports
Replies
1
Views
761
devinali
D

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