Intense Pivot Table

[lynchbro]

Hello,

I am looking to create a pivot table based on massive raw data extracts to create client billing. My goal, is to be able to create a pivot table that in column A, I list out the Fund Family, Pricing Vendor and create subtotals daily based on the individual securities each portfolio is holding. This I figured out, however, the snag is that I need to be able to aggregate the individual securities count in order not to bill out counts for securities owned by multiple portfolios (ie; bill only one charge daily rather than 5 if Apple is owned by 5 portfolios within the fund family).

Raw data columns are as follows:

Position Date

Account Number

Fund Family

Account Name

CUSIP (Pricing) Number

Security Number (Full)

Shares/Par (Full)

Price

Price (Price File)

Price Group

Price Source (Holdings)

Price Type (Holdings)

Price Origin

Fair Value Price Source

Fair Value Price Type

Fair Value Price Processed

Price Code

Foreign Security Indicator

Asset Group

Trading Currency

Income Currency

Segment Description

Category Description

Moody Rating

Pricing Vendors

<tbody>
</tbody>

Here is my Pivot Table beginning, the dates continue right. Count of Security Number (Full)	Position Date
Fund Family	Pricing Vendors	10/2/2017	10/3/2017
ALTEGRIS		1310	1307
ALTEGRIS	AB	412	408
ALTEGRIS	AI	792	793
ALTEGRIS	AP	65	65
ALTEGRIS	AT	30	30
ALTEGRIS	HV	5	5
ALTEGRIS	MP	6	6
(blank)
(blank)	(blank)
Grand Total		1310	1307

<tbody>
</tbody>

 

[lynchbro]

When I modify 10.02.17 data to be unique securities by removing the duplicate security numbers from the raw data for that day, the count changes to:

Count of Security Number (Full)	Position Date
Fund Family	Pricing Vendors	10/2/2017
ALTEGRIS		1112
ALTEGRIS	AB	216
ALTEGRIS	AI	792
ALTEGRIS	AP	65
ALTEGRIS	AT	30
ALTEGRIS	HV	5
ALTEGRIS	MP	4
(blank)
(blank)	(blank)
Grand Total		1112

<tbody>
</tbody><colgroup><col><col><col></colgroup>

 

[lynchbro]

This is my goal, but to not manually remove security number duplicates by date & fund family from the raw data.