Pivot Table - How do I get unique counts of Grand Totals?

Paul M

New Member
Joined
Jan 28, 2004
Messages
16
Hello Guys:

This one has been baffling me: How can I use the data below to generate the (manually adjusted) pivot table shown below it? The unique counts should work for any combination of firm and state selected from the drop-down list. Thanks!
Book2.xls
BCDEFGHI
1Data:
2IDStateFirm
31000NYCitigroup
41001NYLehman
51001NJGoldman
61002CAGoldman
71002DCMerrill
81003NJBearStrn
91003FLJPM
10
11
12DesiredPivotTable
13CountofIDState
14FirmCADCFLNJNYMultipleCodesGrandTotal
15BearStrn11
16Citigroup11
17Goldman112
18JPM11
19Lehman11
20Merrill11
21MultipleCodes-3-3
22GrandTotal11122-34
Sheet1
 

Some videos you may like

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Juan Pablo González

MrExcel MVP
Joined
Feb 8, 2002
Messages
11,959
Re: Pivot Table - How do I get unique counts of Grand Totals

You need to add (at least) one extra column...
Book1
ABCDEFG
1IDStateFirmTempUnique
21000NYCitigroup1000NY1
31001NYLehman1001NY1
41001NJGoldman1001NJ1
51002CAGoldman1002CA1
61002DCMerrill1002DC1
71003NJBearStrn1003NJ1
81003FLJPM1003FL1
9
10
11
12
13
14CountofIDState
15FirmCADCFLNJNYGrandTotal
16BearStrn11
17Citigroup11
18Goldman112
19JPM11
20Lehman11
21Merrill11
22GrandTotal111227
Sheet1


D2 equals:

=A2&CHAR(127)&B2&CHAR(127)

and E2:

=1/SUM(COUNTIF($D$2:$D$8,D2))
 

Paul M

New Member
Joined
Jan 28, 2004
Messages
16
Re: Pivot Table - How do I get unique counts of Grand Totals

Juan,

I want to get a grand total of 4. How can I do that. In my estimation I can only get that by using the sum function instead of the count and include somehow a column of negative multiples. That's the way I have reasoned it out in my head, but I'm not sure how practical it is to achieve.
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
Re: Pivot Table - How do I get unique counts of Grand Totals

Like this?
BOOK13
ABCDEFGHIJKLMN
1IDStateFirmUniqueDupesSumofUniqueState
21000NYCitigroup1-FirmCADCFLNJNYMultGrandTotal
31001NYLehman1-BearStrn11
41001NJGoldman1(1)Citigroup11
51002CAGoldman1-Goldman112
61002DCMerrill1(1)JPM11
71003NJBearStrn1-Lehman11
81003FLJPM1(1)Merrill11
9MultMult(3)Mult(3)(3)
10GrandTotal11122(3)4
Sheet1


The formula in E2 is:

=IF(COUNTIF(A$2:A2,A2)=1,0,-1)

copied down.

The formula in D9 is:

=SUM(E2:E8)
 

Paul M

New Member
Joined
Jan 28, 2004
Messages
16
Re: Pivot Table - How do I get unique counts of Grand Totals

That will work just fine for the data as shown, but how can I make it work for any combination of firm and state?

The real data has about 10,000 records of which say 3,000 are dupes. Also the real data has not 2 but 8 fields, and it should work for any combination as well. I also do a pivot chart on the data.

Thx
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

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
Top