Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 6 of 6

Thread: Count Of Unique Values In Pivot Table

  1. #1
    Join Date
    23rd May 2008
    Posts
    11

    Count Of Unique Values In Pivot Table

    I'm trying to create a pivot table that will count how many employees have completed a Learning Plan. This task becomes complex (for me) because each learning plan has multiple Courses, each Course has a status of "Completed" or "Incomplete". A Learning Plan would only be considered "Completed" if all the courses within that Learning Plan were completed. In doing some research, it looks like I'll need to create another column of data, that shows per employee, per Learning Plan, if the entire Learning Plan has been completed, but I'm not sure of the best way to go about this. Please find SampleData attached.

    Thanks in Advance,

    Margs
    Last edited by Margs; June 19th, 2008 at 06:12.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,084

    Re: Count Of Unique Values For Pivot Table

    Take a look at the Pivot Table and see if this is what you wanted.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  3. #3
    Join Date
    23rd May 2008
    Posts
    11

    Re: Count Of Unique Values For Pivot Table

    Thanks, but that's not exactly what I'm looking for. Ultimately I would like to show how many employees have completed a given Learning Plan. So the Pivot Table should end up displaying the names of all the Learning Plans and the count all of the employees that have completed a Learning Plan. In order for a Learning Plan to be considered to "Completed", all of the courses corresponding to that Learning Plan must have a status of "Completed."

    In order to do this, I think I'll need to create another column in my source data called Learning Plan Completion and the results should show "1" in just one of the rows per employee, per learning plan, only if all of the courses per a given learning plan, per a given employee show as "Completed" in the Course Status column. If any the courses per employee per learning plan show as Incomplete in the Course Status column, then the Learning Plan Completion column should show all "0's." This seems like data setup I'll need in order to build the pivot table I want, but I just don't know/understand the formulas needed to build the Learning Plan Completion column.

    Thanks!

    Margs

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,084

    Re: Count Of Unique Values For Pivot Table

    OK. Right-click on the Pivot Table and choose Wizard from the popup.
    Next, choose "Layout . . ."
    Once the layout dialog is open, in the Row field, click and drag "Employee" off of the layout.
    Next, from the list of available fields on the right side of the layout, drag "Course" onto the Row field (replacing Employee)

    Click OK and you should have what you want. You may need to drop down the page field and choose All to show all Learning Plans in the table.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

  5. #5
    Join Date
    23rd May 2008
    Posts
    11

    Re: Count Of Unique Values For Pivot Table

    AAE,

    Thanks again, but now the table is just the number of employees per course that have completed or have not completed the course (see attached).

    I won't be able to summarize the data I need in a pivot table until I create it. That is, I have to create a column that shows if an entire Learning Plan has been completed per employee (which requires all course per learning plan to have Course Status of "Completed").

    Thanks,

    Margs
    Last edited by Margs; June 19th, 2008 at 06:12.

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    19th January 2007
    Location
    South Carolina, USA
    Posts
    9,084

    Re: Count Of Unique Values For Pivot Table

    You could use Advanced Filter with criteria and filter unique values, with the criteria set to "=incomplete". Though multiple modules in a Learning Plan may be incomplete, the AF will provide only unique instances of the employee and Learning Plan incomplete.

    Use the filtered data as the source for the PT. Use the Learning Plan as the page field and get a count on the status.

    Optionally, you could set up your tracking differently by using three fields: one field for total modules in a Learning Plan, a field for total modules completed and the third for a true/false to evaluate the previous two fields.
    AAE
    ----------------------------------------------------

    Forum Rules | Message to Cross Posters | How to use Tags

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Count Unique Values In Pivot Table
    By dmcdonald in forum EXCEL HELP
    Replies: 12
    Last Post: March 4th, 2008, 00:47
  2. Count Unique Values, For Specific Values
    By CallMeJD in forum EXCEL HELP
    Replies: 4
    Last Post: September 14th, 2006, 10:07
  3. Count Pivot Table Unique Values
    By DWF in forum EXCEL HELP
    Replies: 4
    Last Post: October 2nd, 2004, 04:36
  4. Replies: 3
    Last Post: September 1st, 2004, 15:44

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
  •  
porno