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:

[TABLE="width: 1600"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Position Date
[/TD]
[TD="width: 64, bgcolor: transparent"]Account Number
[/TD]
[TD="width: 64, bgcolor: transparent"]Fund Family
[/TD]
[TD="width: 64, bgcolor: transparent"]Account Name
[/TD]
[TD="width: 64, bgcolor: transparent"]CUSIP (Pricing) Number
[/TD]
[TD="width: 64, bgcolor: transparent"]Security Number (Full)
[/TD]
[TD="width: 64, bgcolor: transparent"]Shares/Par (Full)
[/TD]
[TD="width: 64, bgcolor: transparent"]Price
[/TD]
[TD="width: 64, bgcolor: transparent"]Price (Price File)
[/TD]
[TD="width: 64, bgcolor: transparent"]Price Group
[/TD]
[TD="width: 64, bgcolor: transparent"]Price Source (Holdings)
[/TD]
[TD="width: 64, bgcolor: transparent"]Price Type (Holdings)
[/TD]
[TD="width: 64, bgcolor: transparent"]Price Origin
[/TD]
[TD="width: 64, bgcolor: transparent"]Fair Value Price Source
[/TD]
[TD="width: 64, bgcolor: transparent"]Fair Value Price Type
[/TD]
[TD="width: 64, bgcolor: transparent"]Fair Value Price Processed
[/TD]
[TD="width: 64, bgcolor: transparent"]Price Code
[/TD]
[TD="width: 64, bgcolor: transparent"]Foreign Security Indicator
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Asset Group
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Trading Currency
[/TD]
[TD="class: xl63, width: 64, bgcolor: transparent"]Income Currency
[/TD]
[TD="width: 64, bgcolor: transparent"]Segment Description
[/TD]
[TD="width: 64, bgcolor: transparent"]Category Description
[/TD]
[TD="width: 64, bgcolor: transparent"]Moody Rating
[/TD]
[TD="width: 64, bgcolor: transparent"]Pricing Vendors
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 425"]
<tbody>[TR]
[TD="colspan: 2"]Here is my Pivot Table beginning, the dates continue right.

Count of Security Number (Full)
[/TD]
[TD]Position Date
[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Fund Family
[/TD]
[TD]Pricing Vendors
[/TD]
[TD="align: right"]10/2/2017
[/TD]
[TD="align: right"]10/3/2017
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD][/TD]
[TD="align: right"]1310
[/TD]
[TD="align: right"]1307
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD]AB
[/TD]
[TD="align: right"]412
[/TD]
[TD="align: right"]408
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD]AI
[/TD]
[TD="align: right"]792
[/TD]
[TD="align: right"]793
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD]AP
[/TD]
[TD="align: right"]65
[/TD]
[TD="align: right"]65
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD]AT
[/TD]
[TD="align: right"]30
[/TD]
[TD="align: right"]30
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD]HV
[/TD]
[TD="align: right"]5
[/TD]
[TD="align: right"]5
[/TD]
[/TR]
[TR]
[TD]ALTEGRIS
[/TD]
[TD]MP
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]6
[/TD]
[/TR]
[TR]
[TD](blank)
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](blank)
[/TD]
[TD](blank)
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total
[/TD]
[TD][/TD]
[TD="align: right"]1310
[/TD]
[TD="align: right"]1307
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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:

[TABLE="width: 319"]
<tbody>[TR]
[TD="colspan: 2"]Count of Security Number (Full)[/TD]
[TD]Position Date[/TD]
[/TR]
[TR]
[TD]Fund Family[/TD]
[TD]Pricing Vendors[/TD]
[TD="align: right"]10/2/2017[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD][/TD]
[TD="align: right"]1112[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD]AB[/TD]
[TD="align: right"]216[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD]AI[/TD]
[TD="align: right"]792[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD]AP[/TD]
[TD="align: right"]65[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD]AT[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD]HV[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]ALTEGRIS[/TD]
[TD]MP[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD](blank)[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD](blank)[/TD]
[TD](blank)[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD][/TD]
[TD="align: right"]1112[/TD]
[/TR]
</tbody><colgroup><col><col><col></colgroup>[/TABLE]

 

[lynchbro]

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