Count Unique Values In A Filtered Row with Duplicates
 

Hello,

I've tried to copy and paste below the resource spreadsheet I am working on.
What I want to be able to do is list out each project and who the project
manager is on that project (now some PMs can work on more than one so there
are duplicates). Then I want to be able to subtotal the hours (in case I
want to review just one project, one city, or one PM) but also remove the
duplicates again. Can you help me out please?

THANKS,
Joe



Jan-2006 Feb-2006
Total Hours: 411.00 411.00
Total Resources
Total FTE:
Total Unallocated: 0.00 0.00

Resource Name Role Region Project Jan-2006 Feb-2006
Bob PM Toronto 1 106.00 106.00
John PM Winnipeg 2 0.00 0.00
Joe PM Calgary 3 30.00 30.00
Jimmy PM Toronto 4 80.00 80.00
Sam PM Toronto 2 65.00 65.00
Tom Sr. PM Vancouver 6 65.00 65.00
Bob PM Toronto 7 65.00 65.00

Count Unique Values In A Filtered Row with Duplicates
 

From what you're showing, what you should do is...
(1) set a filter on your database that appears to start at 'Resource Name'
thru Dec-2006.
DataFilterAutofilter
(2) use the Sub-Total function to total your columns. Use SubTotal instead
of SUM because SubTotal will adjust for filtered items while SUM will add up
all hidden and unhidden items.
(3) Filter as many columns as desired for the appropriate analysis. The
SubTotal function will supply the correct totals [SUM will NOT]

HTH,
---
Gary Brown

If this post was helpful, please click the ''Yes'' button next to ''Was this
Post Helpfull to you?''.


"jcpotwor" wrote:

Hello,

I've tried to copy and paste below the resource spreadsheet I am working on.
What I want to be able to do is list out each project and who the project
manager is on that project (now some PMs can work on more than one so there
are duplicates). Then I want to be able to subtotal the hours (in case I
want to review just one project, one city, or one PM) but also remove the
duplicates again. Can you help me out please?

THANKS,
Joe



Jan-2006 Feb-2006
Total Hours: 411.00 411.00
Total Resources
Total FTE:
Total Unallocated: 0.00 0.00

Resource Name Role Region Project Jan-2006 Feb-2006
Bob PM Toronto 1 106.00 106.00
John PM Winnipeg 2 0.00 0.00
Joe PM Calgary 3 30.00 30.00
Jimmy PM Toronto 4 80.00 80.00
Sam PM Toronto 2 65.00 65.00
Tom Sr. PM Vancouver 6 65.00 65.00
Bob PM Toronto 7 65.00 65.00