Results 1 to 10 of 10
  1. #1
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Pivot Table Format (2003)

    hardly used Pivot Tables and wish to understand how to change the table in the attached file, so that:
    I only show the Totals
    And why do some items appear on two lines, eg see code TEPE-643?
    <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Paul Coyle
    Approach love and cooking with reckless abandon

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Table Format (2003)

    Hi Paul

    First welcome to the nightmare called the pivot table <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

    In order of your questions

    1) Only show totals
    Ensure the Pivot Table menu bar is showing (view| Toolbars | pivot table). Click inside the pivot table so that the menu becomes active. Click on Pivot Table | Table Options and then deselect Grand Totals for columns

    2) The reason TEP-643 appears twice is because there are tweo entries with different spellings and formats so sees it as two items under the heading ie

    TEPE INTERDENTAL YELLOW-FINE 0.7mm and

    TEPE INTERDENTAL YELLOW FINE 0.7MM

    one in capitals and has an extra hyphen

    My starters for 10
    Jerry

  3. #3
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Table Format (2003)

    oops forgot to add tidied up data
    Jerry

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (2003)

    TEPE-643 appears on two lines because there are two different Desc values for it, one with YELLOW FINE, the other with YELLOW-FINE (with a hyphen between the words). Excel isn't intelligent enough to know that these two are intended to be the same.

    Which totals do you mean?

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (2003)

    Why are pivot tables a nightmare? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

  6. #6
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Pivot Table Format (2003)

    'cos I always muck 'em up and I have nightmares....just a personal opinion <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Jerry

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (2003)

    Oh. I <img src=/S/love.gif border=0 alt=love width=15 height=15> pivot tables.

  8. #8
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Pivot Table Format (2003)

    Nightmare scmitemare - they are certainly not as intuitive as I was led to believe. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Thanks for your information, it was helpful, and so prompt! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Paul Coyle
    Approach love and cooking with reckless abandon

  9. #9
    4 Star Lounger pccoyle's Avatar
    Join Date
    Apr 2001
    Location
    Auckland, Auckland, New Zealand
    Posts
    535
    Thanks
    3
    Thanked 2 Times in 2 Posts

    Re: Pivot Table Format (2003)

    Yes that's it, as Jerry also pointed out. I was concentrating on the code thinking that this was the trigger for totals.
    In the end I am hoping to achieve just one line per item.
    The purpose of this model is to see what items previously purchased that clients are no longer buying. Our TAPI only displays on screen that past 12 months buying history, whilst our database has information form 2003 onwards.
    I thought (in my ignorance perhaps?) that a pivot table was an easy visual way to do this. Am I correct or is there a better way?
    Paul Coyle
    Approach love and cooking with reckless abandon

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Pivot Table Format (2003)

    You could omit the Desc field from the pivot table. If you don't like that idea, you'll have to make the spelling of Desc uniform.

Posting Permissions

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