+ Reply to Thread
Results 1 to 2 of 2

Pivot tables: Combining data from multiple columns (Excel 2003)

  1. #1
    Registered User
    Join Date
    03-12-2012
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Excel 2003
    Posts
    1

    Pivot tables: Combining data from multiple columns (Excel 2003)

    Hello,

    I've got a question about pivot tables and I hope you can help me out.

    When you look at the black pivot it shows me the count of 'Classification 1', with the count of the succeeding 'Classification 2'.
    So for example for line 1 and 9:

    Number Classification 1 Classification 2
    1 Type A Type B
    9 Type A Type C

    Gives me a pivot table:
    Classification 1 Classification 2 Total
    Type A
    Type B 1
    Type C 1
    Type A Total 2

    'Type A' is chosen two times in 'Classification 1'. Whereby 'Type B' and 'Type C' are both succeeding 'Type A' one time.

    When we look at the rest of the table:
    3 Type D
    4 Type B Type C
    5 Type C
    6 Type C
    7 Type B
    8 Type B Type D
    9 Type A Type C

    We can see that 'Type D' is chosen once as 'Classification 1' at line 3. The Pivot for 'Type D' looks like this:
    Type D (blank) 1
    Type D Total 1
    Whereby there is one 'Type D' chosen as 'Classification 1' and there is no 'Classification 2' specified.

    HOWEVER:
    Type D is also chosen as a 'Classification 2' at line 8.
    At the Pivot this Classification is shown as a succeeding classification of Type B:

    Type B
    Type C 2
    Type D 1
    (blank) 1
    Type B Total 4

    BUT:
    That is not what I want.
    I want to see that 'Type D' is chosen 2 times (line 3 and 8).
    That Type C is chosen 5 times (line 2,4,5,6,9)
    Type B 5 times and Type A 2 time.
    You can see what I mean in the Blue/Orange table. I would like to see that the Classification 1 and 2 field are combined into one total classification.

    And because I use this Pivot table with a large database, it is important that this information is in a 'Pivot Field'.
    So that I can see:

    Count of number of:
    Classification
    Location
    Time
    People etc.
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    02-22-2011
    Location
    Rhône Alpes, France
    MS-Off Ver
    Excel 2007
    Posts
    201

    Re: Pivot tables: Combining data from multiple columns (Excel 2003)

    Hi,
    See attached pivot table done with Excel 2007
    First use the pivot table wizard to create a PT
    Double click on the value in the PT Transfert, you get a new database table
    Then you create the final PT and get the result as you describe
    Hope this helps
    Best regards
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1