modified pivot table using a macro


O

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.
 
Ad

Advertisements

I

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
 
Ad

Advertisements

O

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top