Pivot Table: Do not display blank rows

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,097
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

New Member
Joined
Apr 17, 2009
Messages
40
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.
 

TonyMJ

New Member
Joined
Apr 17, 2009
Messages
40
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.
 

Forum statistics

Threads
1,082,344
Messages
5,364,803
Members
400,814
Latest member
gangstar67

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top