modified pivot table using a macro

[oercim]

Hello,

I need help. I need to create a table using a macro which resembles like pivot table. For example
let , the data is like below on sheet1

A B
--- ----
jane a
jane a
jane b
john a
john b
john b
john d

My aim to create a table that counts the column B according to column A. For example for this sheet, I want to create a table as below:

a b c d e
--------------------------------------------------------
jane 2 1 0 0 0
-------------------------------------------------------
john 1 2 0 1 0


As can be seen from the second table, "c" and "e" does not exist in sheet1, however I want them to appear the resulted table. Let a,b,c,d and e are listed in sheet2 as below:

A
----
a
b
c
d
e

How can I create the resulted table, using the data in sheet1 and sheet2. I will be very glad for any recomendation. Thanks a lot.

 

[isabelle]

hi oercim,

to: Excel 2013

http://office.microsoft.com/en-ca/excel-help/hide-or-display-zero-values-HA102809803.aspx?CTT=1

Hide zero values in a PivotTable report
1.
Click the PivotTable report.
2.
On the Options tab, in the PivotTable Options group, click the arrow
next to Options, and then click Options.
3.
Click the Layout & Format tab, and then do one or more of the following:

Change error display
Check the For error values show check box under Format.
In the box, type the value that you want to display instead of errors.
To display errors as blank cells, delete any characters in the box.

Change empty cell display
Check the For empty cells show check box. In the box, type the value
that you want to display in empty cells. To display blank cells, delete
any characters in the box. To display zeros, clear the check box.


isabelle

 

[oercim]

The problem is not to display zeros in the blank cells. The problem is to display the blank cells in the pivot table. For example, in sheet1 the second column doesnot consist c and e. When I make a pivot table, naturally, thepivot table procedure dpes not recognize c and e, so they dont appear in the pivot table. However, I want them to apperar in the pivot table. How can I manage this? Thanks a lot.