Results 1 to 4 of 4

Pivot Table: Do not display blank rows

This is a discussion on Pivot Table: Do not display blank rows within the Excel Questions forums, part of the Question Forums category; I have a sheet with names and options and I need to create a receipt of each person based on ...

  1. #1
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    966

    Default Pivot Table: Do not display blank rows

    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
    Name Option1 Option2 Option3 Option4 Option5
    Fred 10



    Tina 3


    1
    Chris 6 4 2 1 5
    Example results
    Chris Option1 6

    Option2 4

    Option3 2

    Option4 1

    Option5 5

    Total 18



    Fred Option1 10

    Total 10



    Tina Option1 3

    Option5 1

    Total 4
    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

  2. #2
    New Member
    Join Date
    Apr 2009
    Location
    Southampton, UK
    Posts
    40

    Default Re: Pivot Table: Do not display blank rows

    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.

  3. #3
    Board Regular
    Join Date
    Oct 2008
    Location
    Bedfordshire (UK)
    Posts
    966

    Default Re: Pivot Table: Do not display blank rows

    ow I could kick myself!
    Thanks Tony

  4. #4
    New Member
    Join Date
    Apr 2009
    Location
    Southampton, UK
    Posts
    40

    Default Re: Pivot Table: Do not display blank rows

    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.

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com