Count Unique Values In Pivot Table

[dmcdonald]

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!

[TheDude]

Can you post a sample of the spreadsheet?

Regards,

-Dude

[dmcdonald]

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_

[TheDude]

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

[dmcdonald]

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...

[PCI]

Perhaps this could be a beginning.

[dmcdonald]

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!

[Craig Ottley]

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...

[dmcdonald]

apologies!

[TheDude]

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