Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I repeat labels in Excel for a pivot table field?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I repeat labels in Excel for a pivot table field?
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 SpecialBlanks 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. "sonya" wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
How do I repeat labels in Excel for a pivot table field?
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.. -- steven1001 ------------------------------------------------------------------------ steven1001's Profile: http://www.excelforum.com/member.php...o&userid=30757 View this thread: http://www.excelforum.com/showthread...hreadid=525705 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Workaround for repeating labels in Excel for a pivot table field
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Field Data | Excel Worksheet Functions | |||
Pivot Table Memory Issue - Excel crashes | Excel Discussion (Misc queries) | |||
Excel Pivot Table Appends a '2' to the field data items | Excel Discussion (Misc queries) | |||
How do I repeat a value in a pivot table in Excel? | Excel Worksheet Functions | |||
Help PLEASE! Not sure what answer is: Match? Index? Other? | Excel Worksheet Functions |