Announcement

Collapse
No announcement yet.

Count Unique Values In Pivot Table

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Count Unique Values In Pivot Table

    Hi All!

    I have a worksheet with a list of employees and the workgroup they belong to, along with other data like manager, start dates, etc. Recently a couple of the workgroups were duplicated (change in managers), so these employees are showing up on two rows even though the workgroup has the same name. The only differences in the two rows are the workgroup effective start and end dates. I need to be able to count, in a pivot table, the number of unique employee/workgroup combinations there are per workgroup. I can add columns to the sheet, but it's a dynamic set of data that will grow each time it's refreshed... Please offer any solutions you can think of!

    Thanks!

  • #2
    Re: Count Unique Values In Pivot Table

    Can you post a sample of the spreadsheet?

    Regards,

    -Dude
    The Dude Abides

    Comment


    • #3
      Re: Count Unique Values In Pivot Table

      Hi Dude, Here's an abbreviated sheet, with names changed to protect personal info and some irrelevent columns removed.. I need to get an accurate count of how many "resources" are in each workgroup...

      Thanks!

      D_
      Last edited by dmcdonald; February 28th, 2008, 06:12.

      Comment


      • #4
        Re: Count Unique Values In Pivot Table

        Before I look into this have you checked out any of the other questions in regards to this issue? Especially the ones linked in this thread?

        Count Of Unique Values With Duplicates
        Count Unique Values, For Specific Values
        Count Pivot Table Unique Values
        pivot table+put unique data in 'row area'+count of items in 'data area'

        Regards,

        -Dude
        The Dude Abides

        Comment


        • #5
          Re: Count Unique Values In Pivot Table

          Hi Dude, Yes, I searched for answers before I posted this question, but I haven't found one that addresses both the multiple criteria and the need to get the unique count into a pivot table.. Also I can't have a lookup table because we won't always know when the data changes.. the excel sheet is linked to a sql server and the idea is to completely automate the reporting process...

          Comment


          • #6
            Re: Count Unique Values In Pivot Table

            Perhaps this could be a beginning.
            Attached Files
            Triumph without peril brings no glory: Just try

            Comment


            • #7
              Re: Count Unique Values In Pivot Table

              Hey thanks for tryin PCI! That's not exactly what I'm looking for. I've been trying a few things and have come up with this, though I'm not sure if it will work.. It depends on the sort order of the raw data (which hopefully won't change, I can talk to the database guys), and it depends on whether the range in the sum product will automatically expand as the data does... maybe there is a way to ensure that the sumproduct range expands? Please see attached...

              Thanks again!
              Last edited by dmcdonald; February 28th, 2008, 06:12.

              Comment


              • #8
                Re: Count Unique Values In Pivot Table

                dmcdonald please can you save your file type as excel 97-2003 there's still a few of us here that don't have 2007...

                Comment


                • #9
                  Re: Count Unique Values In Pivot Table

                  apologies!
                  Last edited by dmcdonald; February 28th, 2008, 06:12.

                  Comment


                  • #10
                    Re: Count Unique Values In Pivot Table

                    Dmcdonald i cant view the pivot table as the file is in 03 the table was created in 07...
                    The Dude Abides

                    Comment


                    • #11
                      Re: Count Unique Values In Pivot Table

                      Hey all, sorry about the compatibility issues... the pivot table looks like this:

                      Data
                      Workgroup Count of Resource Sum of Unique Count

                      Workgroup 1 45 27
                      Workgroup 2 7 4


                      perhaps you can recreate?

                      Comment


                      • #12
                        Re: Count Unique Values In Pivot Table

                        If an issue is a dynamique range for the formulae here is a possibility.
                        Workgroup and resource column are preapred as list and named.
                        As list, the range will increase by itself (see the star when the column is selected) and therefore the name will follow.
                        The SUMPRODUCT column has been duplicated with names.
                        Honestly I don't see some difference between the 2 Pivot Table results, included the COUNTA formulaes where a dynamique range can be installed.
                        Attached Files
                        Triumph without peril brings no glory: Just try

                        Comment


                        • #13
                          Re: Count Unique Values In Pivot Table

                          All, thanks for your efforts on this. The issue was solved from the backend. Yay!

                          Comment

                          Working...
                          X