Pivot table (Excel 2016)

mark91345

Board Regular
Joined
Feb 11, 2011
Messages
113
I have two tables I am trying to combine into one pivot table. The first, "Price", has (thousands of) unique symbols which will be my "key".

IJKLM

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Symbol[/TD]
[TD="align: right"]Close[/TD]
[TD="align: right"]Low[/TD]
[TD="align: right"]Volume[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]A[/TD]
[TD="align: right"]$68.69[/TD]
[TD="align: right"]$68.30[/TD]
[TD="align: right"]3,041,728[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]AA[/TD]
[TD="align: right"]$42.64[/TD]
[TD="align: right"]$41.78[/TD]
[TD="align: right"]3,254,640[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]AAAP[/TD]
[TD="align: right"]$81.20[/TD]
[TD="align: right"]$81.10[/TD]
[TD="align: right"]280,212[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]AABA[/TD]
[TD="align: right"]$72.60[/TD]
[TD="align: right"]$72.46[/TD]
[TD="align: right"]6,721,660[/TD]
[TD="align: right"][/TD]

</tbody>
Price



The second table, "Finra", often has duplicate "Symbol" (stock tickers) with the same date, or other times, only one entry per day.

IJKL
SymbolDate
A10/02/17
A10/02/17
AA10/02/17
AA10/02/17
AAAP10/02/17
AAAP10/02/17
AABA10/02/17
AABA10/02/17
AAC10/02/17
AAC10/02/17

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

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

[TD="align: center"]2[/TD]

[TD="align: right"]58,272[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2,389[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]502,427[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]127,118[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]40,018[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]1,959[/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]145,736[/TD]
[TD="align: right"][/TD]

[TD="align: center"]9[/TD]

[TD="align: right"]12,098[/TD]
[TD="align: right"][/TD]

[TD="align: center"]10[/TD]

[TD="align: right"]11,600[/TD]
[TD="align: right"][/TD]

[TD="align: center"]11[/TD]

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

</tbody>
Finra



My goal is to create a Pivot Table and use the information from both tables. For example, I would have all the data, per stock Symbol, plus the SUM of the Short Volume, per day (each day would have its own column, as shown below).

ABCDEFGHIJK
Symbol
AAPL
BTG
MSFT
AMD
DKB

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

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Sum of daily Short Volume[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]Close[/TD]
[TD="align: right"]Low[/TD]
[TD="align: right"]Volume[/TD]
[TD="align: right"]Open[/TD]
[TD="align: right"]High[/TD]
[TD="align: right"]Median Vol[/TD]
[TD="align: right"]10/02/17[/TD]
[TD="align: right"]10/03/17[/TD]
[TD="align: right"]10/04/17[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]68.69[/TD]
[TD="align: right"]68.30[/TD]
[TD="align: right"]3,041,728[/TD]
[TD="align: right"]69.14[/TD]
[TD="align: right"]69.37[/TD]
[TD="align: right"]1,521,250[/TD]
[TD="align: right"]269,180[/TD]
[TD="align: right"]259,252[/TD]
[TD="align: right"]1,069,711[/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]42.64[/TD]
[TD="align: right"]41.78[/TD]
[TD="align: right"]3,254,640[/TD]
[TD="align: right"]41.84[/TD]
[TD="align: right"]42.68[/TD]
[TD="align: right"]3,659,957[/TD]
[TD="align: right"]31,044[/TD]
[TD="align: right"]43,615[/TD]
[TD="align: right"]45,661[/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]81.20[/TD]
[TD="align: right"]81.10[/TD]
[TD="align: right"]280,212[/TD]
[TD="align: right"]81.10[/TD]
[TD="align: right"]81.25[/TD]
[TD="align: right"]387,746[/TD]
[TD="align: right"]90,027[/TD]
[TD="align: right"]205,555[/TD]
[TD="align: right"]427,056[/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]72.60[/TD]
[TD="align: right"]72.46[/TD]
[TD="align: right"]6,721,660[/TD]
[TD="align: right"]73.02[/TD]
[TD="align: right"]73.25[/TD]
[TD="align: right"]6,925,884[/TD]
[TD="align: right"]164,879[/TD]
[TD="align: right"]1,180[/TD]
[TD="align: right"]26,698[/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]9.04[/TD]
[TD="align: right"]8.97[/TD]
[TD="align: right"]150,371[/TD]
[TD="align: right"]9.03[/TD]
[TD="align: right"]9.26[/TD]
[TD="align: right"]143,532[/TD]
[TD="align: right"]154,845[/TD]
[TD="align: right"]481,874[/TD]
[TD="align: right"]845,546[/TD]
[TD="align: right"][/TD]

</tbody>
Sheet5




I thought I could simply create a new Pivot Table (and check the box for "Add this data to the Data Model". Then, I assumed Pivot Table would simply sum up the Symbols with matching dates (it works fine from a single table).

Nope. When I go to Data | Relationships | Manage Relationships | New, and attempt to Create Relationship (using Symbol as key), I get a popup that says "Both selected columns contain duplicate values. At least one of the columns selected must contain only unique values to create a relationship between the tables".

I verified that my "Price" worksheet has NO duplicates; however, my "Finra" sheet must have duplicates (no choice). I am clueless what to do.
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

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