Pivot table - counting data in columns with same heading

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Pivot table - counting data in columns with same heading

  1. #1
    New Member
    Join Date
    Oct 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Pivot table - counting data in columns with same heading

     
    Hi,

    I have a data table that tracks errors being made and there are 4 column headings called 'rejection cause (1-4)' so that multiple errors can be recorded against the same work item. These columns are populated with text info (from a drop down list).

    I'm trying to build a pivot table that tracks the trend of error types made on a weekly basis which I'm fine with, my problem is that I don't know how to combine the data in the 4 columns so that they are treated as one in a pivot table rather than separated out - is this possible?

    Thanks,

    Pete

  2. #2
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot table - counting data in columns with same heading

    Hi

    You can add a new column with formulas to the source table or create a calculated field on the pivot table, as shown below:


    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


  3. #3
    New Member
    Join Date
    Oct 2010
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot table - counting data in columns with same heading

    Thanks for the reply Worf but unfortunately that doesn't fix my issue as my data is text, not numeric. For example, may data table may have 10 lines of info for April. Line one may have RC1 as "calc error" and on the same row, RC2 as "missing info". The next 2 rows in the data table may have RC1 as "calc error" (no info in RC 2, 3 or 4) and the next 7 rows may have RC1 as "missing info", RC2 as "spelling error" and RC3 as "insert ommission"

    I'd like the pivot to tell that in April I had the following errors:
    - 3 calc errors
    - 8 missing info
    - 7 spelling errors
    - 7 insert ommissions


    Hopefully the above makes sense??

    Thanks

  4. #4
    Board Regular Worf's Avatar
    Join Date
    Oct 2011
    Location
    Rio, Brazil
    Posts
    3,107
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Pivot table - counting data in columns with same heading

      
    o Here is one way to do it; tell me if your source data is not arranged like this.
    o Do you need my test workbook?




    Excel 2013 / Windows 8.1 (home)
    Excel 2013 / windows 7 (work)


User Tag List

Tags for this Thread

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