8 Replies Latest reply: Aug 26, 2011 4:08 AM by Anne Duffy RSS

    Pivot table a few questions

      Hi guys

       

      Pivot Tables have never been my friend, I hope someone can help me out.

      I have a QV load coming from excel, where I have a list of activities, then sub categories,the volume of Sub categories, and the time it takes to complete each of these sub categories.

       

      I wish to create a Pivot table

       

      That shows All the Activities, but the top 5 Sub categories, Based on time to complete, then the rating of where this activity is based on Volume.

       

      Please see example this is the source data

       

       

      ActivitySub categoryNumberTime to complete
      ShoppingGrocery1052
      ShoppingFish100155
      ShoppingVeg1527
      ShoppingMeat2233
      ShoppingChicken7277
      ShoppingHousewares1885
      GardeningWatering155
      GardeningPlanting8510
      GardeningPruning5515
      GardeningTriming10020
      GardeningHoeing7225
      GardeningDigging10030

       

      I would wish it pivot to look like the following data;

       

      ActivitySub categoryNumberTime to complete
      ShoppingFish1155
      ShoppingHousewares485
      ShoppingChicken277
      ShoppingGrocery552
      ShoppingMeat333
      GardeningDigging130
      GardeningHoeing325
      GardeningTriming120
      GardeningPruning415
      GardeningPlanting210

       

      So,

       

      The number is now the position it held in the overall run of things

      And only the highest 5 time to completes per category is displayed,

       

      Is this possible, any help or direction would be greatly appreciated

       

      Thanks

      A