Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Pivot Table - supress display of subvalue where no label exists

Posted on 2011-03-02
4
Medium Priority
?
302 Views
Last Modified: 2012-05-11
I am attempting to format a pivot table (which I am quite familiar with), but am having a problem suppressing a particular portion.

The data analyzed is three levels deep. It will always be coded at the first level, and may, or may not be coded at the second or third levels. (If coded at the third level it MUST be coded at the second level as well.)

My problem is that, when not coded at the third level the table still shows a value of "blank" of course, with the sum of the amount. I want it just to stop at the second level. See pictures below:
 
 As it appears now
 As I'd like it
However, when I filter to suppress  label = (balnk), the second level also gets suppressed, so that no data shows.

To be clear: I want to suppress the lowest level(s) of blank data, but preserve their presentation at the next-highest level if also non-blank.

Can't figure out how to do this!!
0
Comment
Question by:taxbuster
4 Comments
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 1000 total points
ID: 35025457
The only way I think you can do that is with an autofilter on the third level. See attached workbook where I set the third level to appear in its own column and then applied the autofilter. The downside is you'll need to refresh the filter if the data changes.
FilterBlanks.xlsx
0
 

Author Comment

by:taxbuster
ID: 35341110
It's not really a solution, but it IS an answer. Not sure otherwise what to do here!!
0
 
LVL 24

Expert Comment

by:Tracy
ID: 35356961
This question has been classified as abandoned and is being closed as part of the Cleanup Program. See my comment at the end of the question for more details.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question