Pivot Tables - Count number of unique Projects

P

pnewman

New Member
Joined
May 13, 2008
Messages
2
Hi, I have some time card with the following fields:
date, name, project, hours

If my sample data looks like this:

<TABLE style="WIDTH: 126pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=167 border=0 x:str><COLGROUP><COL style="WIDTH: 43pt; mso-width-source: userset; mso-width-alt: 2084" width=57><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 42pt; mso-width-source: userset; mso-width-alt: 2048" width=56><COL style="WIDTH: 11pt; mso-width-source: userset; mso-width-alt: 512" width=14><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 43pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=57 height=17 x:num="39508">3/1/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 30pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=40>paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 42pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=56>proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 11pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right width=14 x:num>3</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39508">3/1/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39508">3/1/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-C</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>4</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39508">3/1/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">robert</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39508">3/1/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">robert</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39508">3/1/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">gary</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39509">3/2/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>7</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39509">3/2/2008 </TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39509">3/2/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-B</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>1</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39509">3/2/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">robert</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">prob-C</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39510">3/3/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">paul</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39510">3/3/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">robert</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl22 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right height=17 x:num="39510">3/3/2008</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">gary</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent">proj-A</TD><TD style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" align=right x:num>8</TD></TR></TBODY></TABLE>

Notice in particular that on 3/2, Paul has 2 records for proj-A (which is allowed in my situation). How can i setup a pivot table that tells me the number of unique projects each person worked on each day. So, I'm looking for a table that looks like:

date name count
3/1/2008 gary 1
paul 3
robert 2
3/2/2008 paul 2
robert 1
3/3/2008 gary 1
paul 1
robert 1


By the way, my real world application has 200 employees, 500 projects, and 47,000 time card entries. I can do pivot tables for all the other data i need; just not this particular statistic.

Many thanks.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
I don't know how familiar you are with pivot tables, but I'll assume that you know how to make one.

You can have the Date/Name in the Row Fields. Then put the project in the data field. Right click on the Project and select field settings. Then you can select Count. This will count how many distinct "projects" there are.

Let me know if you have any other questions.
 
Upvote 0
Hi,

You've specifically asked for a pivot table but I'll quickly mention that there are other ways, such as a query table.

Close your source data file. Open a new workbook, go via menu data, pivot table, external data source, next. Hit the 'get data' button and then Excel files as your database and OK, in the 'select workbook' dialog box select the source data file, OK. If you have not used a defined name for the data source you will get a message "this data contains no visible tables". OK to this and then in the query wizard dialog box hit 'options', select 'system tables' and then OK. Now you should see the worksheets from the source file listed on the L:HSdropdown details for the particular data worksheet, select the fields you want in the pivot table. They will list on the RHS. Keep hitting next until you get the last window (which has a finish option) and then take the second choice to view data or edit query in Microsoft Query. Then hit finish. In MS Query, select a 'cell' in the project field and then menu view, query properties. Select unique values only and OK. Now the 'open door' button to return the data (exit MS Query). Now proceed normally. Either hit finish & make the pivot table or go one step at a time making the pivot table using the wizard. Now you can open the source data file and move the new worksheet (containing the pivot table) into your source data file.

A little long winded to explain!

regards, Fazza
 
Upvote 0
aclare - Thanks for the reply. Yes, I've tried your suggestion before but the problem is that "count" is saying on 3/2 that paul worked on 2 "proj-A"'s. My goal would be to get that to one.
 
Upvote 0
Fazza,

I tried your suggestion and it does work, so, yay for that.

But it seems like there ought to be a way to count unique values in the original file, ya know. I have a PT in the orig file so I can go in and produce any cross-tab I want. But if ever I need unique counts, I have to create a new file? Not the most convenient thing. It isn't like people have not been asking for this ability for a long time. /rant

<b>However, I appreciate that there is a way to do what I need to do. So thank you very much.</b>

p.s. I think your step-by-step was based on 2003, no? I had to do it quite a bit differently in 2007. Technically, it might actually be a little easier in 2007, but not as easy as doing it in the orig PT.
 
Upvote 0
To be honest if you want to use PT I'd create additional data fields for this...

Continuing your example, to avoid using SUMPRODUCT lots of times I'd first create a concatenation of A:C in col E and then use a formula in F to establish project count which you could then sum as per any other data field.

See below for screenshot, apologies if this is not possible in your real scenario but I don't think you can do it any other way using standard PT functionality.

Excel Workbook
ABCDEF
1datenameprojectotheruniqueprojectcnt
203/01/2008paulproj-A339450paulproj-A1
303/01/2008paulproj-B139450paulproj-B1
403/01/2008paulproj-C439450paulproj-C1
503/01/2008robertproj-A739450robertproj-A1
603/01/2008robertproj-B139450robertproj-B1
703/01/2008garyproj-A839450garyproj-A1
803/02/2008paulproj-A739481paulproj-A1
903/02/2008paulproj-A139481paulproj-A0
1003/02/2008paulproj-B139481paulproj-B1
1103/02/2008robertprob-C839481robertprob-C1
1203/03/2008paulproj-A839510paulproj-A1
1303/03/2008robertproj-A839510robertproj-A1
1403/03/2008garyproj-A839510garyproj-A1
Sheet1


Sample formulas which could be copied down for all rows.

Excel Workbook
EF
239450paulproj-A1
Sheet1


HTH ?
 
Upvote 0
Yeah, that works too. I've actually used that one when the distinctiveness is based on only a couple of fields. However, one of the joys of a PT is the ability to add/change your row and columns at will (and, consequently, the 'distinctive' criteria.) With either solution presented here, you lose that imediateness of your changes. If it is just me using the data, sure, that is fine. But thinking about other people who may not be as comfortable with Excel, it wouldn't work so well. Why, oh why, is there not a standard "count distinct" function in Excel for use in pivot tables?
 
Upvote 0
ExcelChick said:
Fazza,

I tried your suggestion and it does work, so, yay for that.

But it seems like there ought to be a way to count unique values in the original file, ya know. I have a PT in the orig file so I can go in and produce any cross-tab I want. But if ever I need unique counts, I have to create a new file? Not the most convenient thing. It isn't like people have not been asking for this ability for a long time. /rant

However, I appreciate that there is a way to do what I need to do. So thank you very much.

p.s. I think your step-by-step was based on 2003, no? I had to do it quite a bit differently in 2007. Technically, it might actually be a little easier in 2007, but not as easy as doing it in the orig PT.
Click to expand...

Great news that it worked for you.

You mention that it is not convenient about creating a new file. I wonder if you noticed the final sentence in the main paragraph of my earlier post "Now you can open the source data file and move the new worksheet (containing the pivot table) into your source data file."? Although the new query or pivot table is created on a new workbook, this new worksheet can be moved from there into the original file. So it does end up in the same file as the data. OK?

The reason for using the separate file is that Excel can have a 'memory leak' when querying open files - suggest google for more information. So by having a separate file this is avoided. Suggest you try creating the result in the source file directly. It may work. It might not. It is just a one off when creating the query table / PT so not too big an issue.

I don't understand your comments about people have been asking for the ability to count unique values for a long time, as it has been available for a long time too. I think it is just some people don't know how to do it?

Pivot tables are great - super flexible & dynamic. Because of the tremendous range of what a pivot table can do it is difficult to include every possibility in the 'simple' wizard interface. To be able to 'drive' such a high powered tool as a PT with particular results for every situation requires being able to work outside the wizard. It isn't that pivot tables can't do some of the things asked of them, it is that some users don't know how to do what they want.

Regards, Fazza
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Function to Paste Table into Outlook Email Resulting in HTML language in Email
Replies
8
Views
614
smozgur
smozgur
E
  • Question Question
VBA code clean up
Replies
9
Views
573
mumps
mumps
B
  • Question Question
Excel Pivot Table to see the number of "Lost Calls"
Replies
0
Views
68
Bernie Galve
B
V
  • Solved
How to put a space in this table in VBA?
Replies
1
Views
501
VBAEXCELNew
V
G
  • Question Question
Using Excel formula to create HTML code text referencing specific cells
Replies
0
Views
1K
gvsumich
G

Forum statistics

Threads
1,224,507
Messages
6,179,172
Members
452,893
Latest member
denay

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top