Hierarchical data structure in pivot - blank rows

Question

I have a data set with a structure in style of:

Fruit     Type           Organic      Sales
Banana                                15
Apple     Green Apple    Organic      20
Apple     Red Apple      Non-Organic  25
Apple     Red Apple      Organic      17
Orange    California                   8
Orange    Spain                        5

In other words, the fruits have different levels of detail. Say I want to see sales for bananas and organic green apples; this would require me to put Fruit, Type and Organic in row labels and Sales in Values. However the problem arises in that Pivot shows two rows of blank values for Banana, repeating the value of Sales. How can I make Pivot understand that blank values mean that the item is not specified further and it should stop listing values?

A picture to illustrate my point:

Answer 1

Well, you can click on the ⊟ to the left of “Banana”, “California”, and “Spain” to collapse the subordinate rows:

   

But of course you don’t want to do that manually.  You can automate it with VBA: select the first column of the pivot table and run this code:

For Each c In Selection
    If c.Value = "(blank)" Then
        c.Offset(-1, 0).ShowDetail = False
    End If
Next c

If you don’t know how to use VBA, see How do I add VBA in Microsoft Office?