How do I repeat labels in Excel for a pivot table field?

S

sonya

I can't figure out how to show a label on a pivot table without hiding
duplicates. For example, instead of the standard pivot summary:

Part Number Work Center Qty
ABC 1 20
2 35
XYZ 5 40
8 20
I want to see it like this with all information shown (nothing compressed):
ABC 1 20
ABC 2 20
XYZ 5 40
XYZ 8 20
 
G

goofy11

One option is to copy the entire pivot table, then paste the values and
formatting into a new worksheet. Then highlight column A (part number).
Use ctrl+G to get to the 'Go To' menu. Then select Special>Blanks to
highlight all the blank cells. Now type "=" then press the up arrow. Then
press ctrl+enter. This technique quickly gets the results of a pivot table
into the format you're after.
 
S

steven1001

And another option is to add a helper column with a value like R001,
R002 to Rnnn and display it the pivot table as the last field in ROW.
Then you will get one row for every value in the underlying data up
until you hit the max number that the pivot table will deal with (dont
know that number)

hide the column if you dont want to see/print it.

regards..
 
C

Cheryl Morris

Please see below for an example of a workaround:

=== Start of original email ============================
title : How do I repeat labels in Excel for a pivot table field?
author : sonya <[email protected]>
date : Thu, 23 Mar 2006 07:29:41 -0800

content : I can't figure out how to show a label on a pivot table without
hiding
duplicates. For example, instead of the standard pivot summary:

Part Number Work Center Qty
ABC 1 20
2 35
XYZ 5 40
8 20
I want to see it like this with all information shown (nothing compressed):
ABC 1 20
ABC 2 20
XYZ 5 40
XYZ 8 20
=== End of original email ==========================

Hi Sonya,

Let's say that the data you wanted to put in a PivotTable looked like this:
Part Number Work Center Qty
ABC 1 10
ABC 1 10
ABC 2 5
ABC 2 10
XYZ 5 40
XYZ 8 5
XYZ 8 15

I suggest creating a Super Label by concatenating "Part Number" and "Work
Center" thus:
Part Number Work Center Concatenate Qty
ABC 1 ABC1 10
ABC 1 ABC1 10
ABC 2 ABC2 5
ABC 2 ABC2 10
XYZ 5 XYZ5 40
XYZ 8 XYZ8 5
XYZ 8 XYZ8 15

Next, create a PivotTable using "Concatenate" as the first PivotTable Row,
the "Part Number" as the 2nd PT Row and the "Work Center" as the 3rd PT Row.

Select the 'Qty" as the PT Column and Sum it.

You'll then have to copy the PT's values and delete the lines you don't want.
Spot-check some of the rows and the Totals.

url:http://www.ureader.com/msg/103432861.aspx
 
Top