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!
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 |