Groups

    Excel pivot table question

    Hi,

    First off, apologies for asking this on HUKD, I know some get annoyed by non-deal related questions, but I kn0w there are a lot of technical people on here too.

    I'm trying to set up a pivot table and am struggling to get what I want out of it. Ideally I want a pivot table with a predefined row data, more details below

    I have a couple of hundred rows and each detailing an event. There are 6 columns which each describe part of that event (say part a-f). All the data in all of these columns is one of 7 numbers (lets say 1 - 7). I want a table to be able to count the instances of each number in all of the columns.

    So something like:-

    a b c d e f

    1 1 1 2 3 5

    1 2 3 3 3 5

    2 2 2 3 3 4

    2 3 1 4 5 5

    Would go to:-

    # a b c d e f

    1 2 1 2 0 0 0

    2 2 2 1 1 0 0

    3 0 1 1 2 3 0

    4 0 0 0 1 0 1

    5 0 0 0 0 1 3

    I know I can do this on a countif, but there are another bunch of columns I need to have as filters (again, I know I can use countifs, but was hoping to avoid given the number of filters I need...)

    Thanks for any tips!

    E.M.

    2 Comments

    try Mr excel or stack overflow.. pivots do have calculated fields that might do what you want off top of my head..

    Original Poster

    Ta,

    I'd forgotten about stack overflow. I tracked down the exact same thing I was looking to do - turns out it can't be done...

    Saved me an hour or so of fruitless trying though!
    Post a comment
    Avatar
    @
      Text
      Top Discussions
      1. Which Kodi Add-ons?1415
      2. Rain effect window. The one from FRIENDS! Please help!78
      3. Battery drain on iOS 111315
      4. Marvel characters11

      See more discussions