anationalacrobat
Technical User
Yes, there are kludge workarounds but nothing that would work so well as proper support. UGH! What's it going to take to get this feature?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Excel 2010 and still no distinct count on pivot tables
- Thread starter anationalacrobat
- Start date
- Status
Are you using the MS customer experience/feedback system they have in the Beta? If so, mention it to them via that tool. it can't hurt, whether it happens or not. ![[wink]](/data/assets/smilies/wink.gif "[wink] [wink]")
Forgive me for being dense, but could you further explain what you mean in your comment/rant? Are you SURE it's something that can't be done.... or are you SURE it's something that SHOULD be done?
I suppose you mean so that you can eliminate duplicates based on one column of your data. I'd guess that could be accomplished via grouping, so long as the records are true duplicates.
--
"If to err is human, then I must be some kind of human!" -Me
Forgive me for being dense, but could you further explain what you mean in your comment/rant? Are you SURE it's something that can't be done.... or are you SURE it's something that SHOULD be done?
I suppose you mean so that you can eliminate duplicates based on one column of your data. I'd guess that could be accomplished via grouping, so long as the records are true duplicates.
--
"If to err is human, then I must be some kind of human!" -Me
- Thread starter
- #3
anationalacrobat
Technical User
the Excel developers said it's their most requested new feature and no, they won't be adding it. But ooh, look! Tighter Sharepoint integration. Shiny.
Here's what I'm talking about:
Count Unique Items
In an Excel pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.
For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'
In the first data row, enter a formula that refers to the customer and item columns. For example:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)
Copy the formula down to all rows in the database.
Then, add the field to the data area of the Excel pivot table.
In this example, you can see that nine unique customers placed an order for binders, and there were 14 orders for binders.
The problem is that these calculations can really slow down a sheet. And you have to create a custom formula for creating distinct counts in different timespans. The example I'm working on is an attendance list for events. Sheet 1 are the names of people, sheet 2 links the names from sheet 1 with activities and a date. I can then run a pivot table to summarize those attendances. The problem comes in when trying to do my counts.
1. How many times has someone attended an activity in a year?
2. How many unique participants do we have in a month?
3. How many unique participants in each activitity do we have?
4. How many unique participants in the year?
I have to have a separate summary column for each count and it becomes a nightmare. The people working with the sheet love the ease of data entry but this whole thing should have probably been done in Access. Those extra counts weren't in the original request but got added in halfway trhough the project.
Here's what I'm talking about:
Count Unique Items
In an Excel pivot table, you may want to know how many unique customers placed an order for an item, instead of how many orders were placed. A pivot table won't calculate a unique count. However, you could add a column to the database, then add that field to the pivottable.
For example, to count the unique occurences of a Customer/Item order, add a column to your database, with the heading 'CustItem'
In the first data row, enter a formula that refers to the customer and item columns. For example:
=IF(SUMPRODUCT(($A$2:$A2=A2)*($C$2:$C2=C2))>1,0,1)
Copy the formula down to all rows in the database.
Then, add the field to the data area of the Excel pivot table.
In this example, you can see that nine unique customers placed an order for binders, and there were 14 orders for binders.
The problem is that these calculations can really slow down a sheet. And you have to create a custom formula for creating distinct counts in different timespans. The example I'm working on is an attendance list for events. Sheet 1 are the names of people, sheet 2 links the names from sheet 1 with activities and a date. I can then run a pivot table to summarize those attendances. The problem comes in when trying to do my counts.
1. How many times has someone attended an activity in a year?
2. How many unique participants do we have in a month?
3. How many unique participants in each activitity do we have?
4. How many unique participants in the year?
I have to have a separate summary column for each count and it becomes a nightmare. The people working with the sheet love the ease of data entry but this whole thing should have probably been done in Access. Those extra counts weren't in the original request but got added in halfway trhough the project.
Well, if you're stucking handling it in Excel, is it possible to use MS Query to get what you need instead of formulas/pivot tables? I've not used it very often, but I know that when it can be used, it does seem to offer some performance benefits.
--
"If to err is human, then I must be some kind of human!" -Me
--
"If to err is human, then I must be some kind of human!" -Me
- Status