Disappearing Pivot Table Fields
Prep for a pre-employment Excel test with Job Test Prep
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Disappearing Pivot Table Fields

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Mike T.
    Posts
    180
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Question:

    There's basically two ways of preparing databases pre-Pivot Table.

    You can keep all your data points (sales figures) in the same Row and use the Column (Field) Heading then go straight to a PT. This keeps the number of Excel Rows at a minimum since all data points are in the same Row.
    The other way is to transpose the Column Heading (Fields) of the data points so that all Data Point Column Headings (Fields) are in one column and the Data Points (sales Figures are in an adjacent new Column and a new database. This creates uniqueness.
    Drawback to method two: If you have 11 data points in one Row, and you transpose the Field Headings, you now use up 11 Rows for each Product Name since each data point is in its'own unique Row. This fills up Excel real fast.

    The first method, which is quick to construct and convert to a PT takes up minimal Excel Rows.

    The second method, which PTs prefer, can use many, many more Rows, including quickly approaching 65k and requires a 2nd database.

    Now, the main question: Using method one, when I go from the origihal data base to the PT, my Column Fields disappear when I deselect them. They are still on the Wizard, but I always have to go back and reinstall.

    How do I get around this? Where do they go and why?

    The second method, which is the cleanest, creates a second database, then converts to PT. The Fields are are always there but I can't do Calculations. PT likes this method but it pushes Excel specs and forces me to go to Access for data storage.

    Yes, this is a long Post, but I need to understand how to get around these constraints, or fix my apparant lack of understanding.

    Thanks ahead of time.

    Henry (mtaylor2@satx.rr.com)

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Henry, your data really should be in 1st Normal Form (See http://www.stuart.iit.edu/courses/im...tabase/1NF.htm) -- what you describe as "the 2nd method". If 1NF results in more than 65,536 records you should be using Access (or some other database). I'm sorry to say, I can't reproduce your difficulty with "the 1st method" -- a listing with a repeating group.

    Please understand that the justification for 1NF goes well beyond convenience. It allows you to make comparisions and do calculations that you couldn't do otherwise.

    [ This Message was edited by: Mark W. on 2002-04-19 08:09 ]

User Tag List

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