Pivot Table: Do not display blank rows

[MartinL]

I have a sheet with names and options
and I need to create a receipt of each person based on their choices
However I do not want to print out blank choices as there are over 300 of these and each name may choose between 1 or more.
Usually no more than 6 but i need to take into account the guy who goes ballistic.

example data

<table x:str="" style="border-collapse: collapse; width: 288pt;" border="0" cellpadding="0" cellspacing="0" width="384"><col style="width: 48pt;" span="6" width="64"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 48pt;" width="64" height="17">Name</td> <td style="width: 48pt;" align="center" width="64">Option1</td> <td style="width: 48pt;" align="center" width="64">Option2</td> <td style="width: 48pt;" align="center" width="64">Option3</td> <td style="width: 48pt;" align="center" width="64">Option4</td> <td style="width: 48pt;" align="center" width="64">Option5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Fred</td> <td x:num="" align="center">10</td> <td align="center">
</td> <td align="center">
</td> <td align="center">
</td> <td align="center">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Tina</td> <td x:num="" align="center">3</td> <td align="center">
</td> <td align="center">
</td> <td align="center">
</td> <td x:num="" align="center">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Chris</td> <td x:num="" align="center">6</td> <td x:num="" align="center">4</td> <td x:num="" align="center">2</td> <td x:num="" align="center">1</td> <td x:num="" align="center">5</td> </tr> </tbody></table>

Example results

<table x:str="" style="border-collapse: collapse; width: 174pt;" border="0" cellpadding="0" cellspacing="0" width="231"><col style="width: 74pt;" span="2" width="98"> <col style="width: 26pt;" width="35"> <tbody><tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt; width: 74pt;" width="98" height="17">Chris</td> <td style="width: 74pt;" width="98">Option1</td> <td style="width: 26pt;" x:num="" align="right" width="35">6</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Option2</td> <td x:num="" align="right">4</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Option3</td> <td x:num="" align="right">2</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Option4</td> <td x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Option5</td> <td x:num="" align="right">5</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24">Total</td> <td class="xl24" x:num="" x:fmla="=SUM(C1:C5)" align="right">18</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Fred</td> <td>Option1</td> <td x:num="" align="right">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24">Total</td> <td class="xl24" x:num="" align="right">10</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24">
</td> <td class="xl24">
</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">Tina</td> <td>Option1</td> <td x:num="" align="right">3</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td>Option5</td> <td x:num="" align="right">1</td> </tr> <tr style="height: 12.75pt;" height="17"> <td style="height: 12.75pt;" height="17">
</td> <td class="xl24">Total</td> <td class="xl24" x:num="" align="right">4</td> </tr> </tbody></table>

I started looking at Pivot tables but could not get rows with a blank option to not show.
Then thought about arrays
Then I got lost

 

[TonyMJ]

You could still use a pivot table and then apply an auto filter to show only Non Blanks.

Once the PV is created and looks they way you want select a cell adjacent to it (if PV is A3:C9 select D4) and then turn on auto filter (Data > Filter > AutoFilter). The drop down arrows should appear in you headings and you can filter away.

 

[MartinL]

ow I could kick myself!
Thanks Tony

 

[TonyMJ]

No problem. Often the answer is hiding in plain sight!!!

The only watch out is the filter doesn't update when you refresh the pivot. You need to remember that extra manual step.