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
1SymbolCloseLowVolume
2A$68.69$68.303,041,728
3AA$42.64$41.783,254,640
4AAAP$81.20$81.10280,212
5AABA$72.60$72.466,721,660

<tbody>
</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
1SymbolDateShort Volume
2A10/02/1758,272
3A10/02/172,389
4AA10/02/17502,427
5AA10/02/17127,118
6AAAP10/02/1740,018
7AAAP10/02/171,959
8AABA10/02/17145,736
9AABA10/02/1712,098
10AAC10/02/1711,600
11AAC10/02/1717

<tbody>
</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
1Sum of daily Short Volume
2SymbolCloseLowVolumeOpenHighMedian Vol10/02/1710/03/1710/04/17
3AAPL68.6968.303,041,72869.1469.371,521,250269,180259,2521,069,711
4BTG42.6441.783,254,64041.8442.683,659,95731,04443,61545,661
5MSFT81.2081.10280,21281.1081.25387,74690,027205,555427,056
6AMD72.6072.466,721,66073.0273.256,925,884164,8791,18026,698
7DKB9.048.97150,3719.039.26143,532154,845481,874845,546

<tbody>
</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

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Forum statistics

Threads
1,213,527
Messages
6,114,150
Members
448,552
Latest member
WORKINGWITHNOLEADER

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