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

[Paul M]

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
	B	C	D	E	F	G	H	I
1	Data:
2	ID	State	Firm
3	1000	NY	Citigroup
4	1001	NY	Lehman
5	1001	NJ	Goldman
6	1002	CA	Goldman
7	1002	DC	Merrill
8	1003	NJ	BearStrn
9	1003	FL	JPM
10
11
12	DesiredPivotTable
13	CountofID	State
14	Firm	CA	DC	FL	NJ	NY	MultipleCodes	GrandTotal
15	BearStrn				1			1
16	Citigroup					1		1
17	Goldman	1			1			2
18	JPM			1				1
19	Lehman					1		1
20	Merrill		1					1
21	MultipleCodes						-3	-3
22	GrandTotal	1	1	1	2	2	-3	4
Sheet1

 

[Juan Pablo González]

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

You need to add (at least) one extra column...

Book1
	A	B	C	D	E	F	G
1	ID	State	Firm	Temp	Unique
2	1000	NY	Citigroup	1000NY	1
3	1001	NY	Lehman	1001NY	1
4	1001	NJ	Goldman	1001NJ	1
5	1002	CA	Goldman	1002CA	1
6	1002	DC	Merrill	1002DC	1
7	1003	NJ	BearStrn	1003NJ	1
8	1003	FL	JPM	1003FL	1
9
10
11
12
13
14	CountofID	State
15	Firm	CA	DC	FL	NJ	NY	GrandTotal
16	BearStrn				1		1
17	Citigroup					1	1
18	Goldman	1			1		2
19	JPM			1			1
20	Lehman					1	1
21	Merrill		1				1
22	GrandTotal	1	1	1	2	2	7
Sheet1

D2 equals:

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

and E2:

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

 

[Paul M]

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]

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

Like this?

BOOK13
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	ID	State	Firm	Unique	Dupes		SumofUnique	State
2	1000	NY	Citigroup	1	-		Firm	CA	DC	FL	NJ	NY	Mult	GrandTotal
3	1001	NY	Lehman	1	-		BearStrn				1			1
4	1001	NJ	Goldman	1	(1)		Citigroup					1		1
5	1002	CA	Goldman	1	-		Goldman	1			1			2
6	1002	DC	Merrill	1	(1)		JPM			1				1
7	1003	NJ	BearStrn	1	-		Lehman					1		1
8	1003	FL	JPM	1	(1)		Merrill		1					1
9		Mult	Mult	(3)			Mult						(3)	(3)
10							GrandTotal	1	1	1	2	2	(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]

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