Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    Hello,

    I've got a question about Pivot Tables. This is exactly the same as the question here:
    http://windowssecrets.com/forums/sho...s-(Excel-2003)

    This question is already answered, however the attachements are missing in the thread. Therefore I can't totally figure out how the problem is solved.

    Can someone help me figure it out?

    Thanks,
    Misha Grift

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 341 Times in 334 Posts
    I would recommend posting an example workbook explaining what you need and the answer can be "recreated" for you.Steve

  3. #3
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    That would make it a lot clearer indeed.

    The pivot in black is how I see it now. The one in red is how I can see the total number for classification 1 and 2, but the problem with that one is that it doesn't give me the total number and it is difficult to add other pivot fields.
    The way I would like to see it, is the one in green. Whereby the two classification fields are combined into one 'classification total' field.
    Attached Files Attached Files

  4. #4
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts
    Hi Misha

    You could use a simple formula to get your results.
    see attached file
    You could use a 'named range' for the data block.
    ..or you could use a dynamic range name for the data block.
    ..or you could use a pre-defined 'big' range in the COUNTIF formula for the source data block
    e.g. Database!$B$1:$C$5000

    zeddy
    Attached Files Attached Files

  5. #5
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Zeddy,

    Thanks for the support, but that's not really what I want to do.
    I want to put your information in a pivot table. The problem with pivots is that you can't refer to cells and names.

  6. #6
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts
    Hi Misha

    It is not clear what you want to do.
    You say "The one in red is how I can see the total number for classification 1 and 2".
    This is NOT true.

    The Count of Classification 2 in red is telling you how many non-blank entries there are in the Classification 2 column for each entry in the Classification 1 column.
    So for example, in the red pivot table, for Type B, the Count of Classification shows as 3 (because there are 3 non-blank entries in column [C] in the source data when column [B] is Type B).
    Yet, in column [C] there is actually only 1 entry for Type B.

    You could see this effect more clearly if, for example, in cell [c8] of your sheet [Database] you typed an entry of Type F and then refreshed your pivot tables.

    zeddy

  7. #7
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Hi Zeddy,

    I see, I made a mistake. However my question is the same.
    I will try to make it clearer to you:

    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. There is no line whereby 'Type A' is chosen as 'Classification 1' and there is a blank 'Classification 2'.

    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 4 times (line 4,5,6,9)
    Type B 3 times and Type A 1 time.

    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
    Classification Location Time People etc.

  8. #8
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts
    Hi Misha

    Re:
    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 4 times (line 4,5,6,9)
    Type B 3 times and Type A 1 time.

    In your Example.xls file sent:
    'Type D' is chosen 2 times (line 3 and 8). YES
    Type C is chosen 5 times (line 2,4,5,6,9)
    Type B 5 times and Type A 2 times.

    zeddy

  9. #9
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    Huh? That's strange... The info in the file sent is correct :

    BUT:
    That is not what I want.
    I want to see that 'Type D' is chosen 2 times (line 3 and 8).
    Type C is chosen 5 times (line 2,4,5,6,9)
    Type B 5 times and Type A 2 times

  10. #10
    Bronze Lounger
    Join Date
    Mar 2002
    Location
    Newcastle, UK
    Posts
    1,520
    Thanks
    32
    Thanked 178 Times in 173 Posts
    Hi Misha

    I think what you are asking for is like the layout in blue in the attached file.

    This type of layout cannot be created via a Pivot table.

    zeddy
    Attached Files Attached Files

  11. #11
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    And something like this:
    Whereby a formula is used to count them?
    Attached Files Attached Files

  12. #12
    New Lounger
    Join Date
    Mar 2012
    Posts
    7
    Thanks
    0
    Thanked 1 Time in 1 Post
    I've found it out myself!

    The way I did it (if someone might search for the same) is using consolidate.
    Whereby the two columns have the same column name. So by not using Classification 1 and 2, but just two columns called Classification.

    The only thing is that with consolidate, the pivot table can't really be used like it normally works with multiple pivot fields, but now I can count the total amount.

    Still thanks for the help.

    Greetings

  13. The Following User Says Thank You to Ahsimmis For This Useful Post:

    simongrolin (2014-05-27)

Posting Permissions

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