Count Unique Values in a Pivot Table

[audiate]

Hello there

I have a spreadsheet with several records for each person's name.
I want to have pivot tables based on various columns, with the data field being a count of unique occurrences of a person's name.

When I set up a basic Pivot, it counts each occurence of the person's name.

Is there some way to have it count unique values only?

Thanks

 

[J Meyer]

The sort method is probably the easiest on the CPU, but I still find the "1/countifs(...)" formula the best solution. I usually do the calulation and paste values over the formula since it is quite a resource hog.

We use this in a lot of different files, so the sort method would be tedious. I still think the pivot should be able to do this!

Thanks for you input.

 

[jeff160]

nvm. my idea didn't work...

 

[lazyvba]

Hello,
I did implement this feature ( unique count as a data field) in pivot in excel 2007.
You just have to paste the code and choose the field where you want to count unique.
The advanatge is that this is adaptable to most pivot tables, and recalculate according to filters, row and columns fields.

let me know if it's working for you
http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

regards

 

[jawgee]

Thanks lazyvba!! Your solution worked for me and has a nice elegant outcome that doesn't require manipulation of the data sheet.

You had mentioned in your blog that the totals don't calculate correctly (which is why you tried to hide them), but they seemed to work out perfectly for me. What's the problem with totaling the numbers?

Thanks,
jawgee

 

[lazyvba]

Thanks for the positive feedback Jawgee.
Regarding the totals, it could be right in some simple situations. The problem is that the total adding counts of unique values, and therefore will be usually wrong.
I.E if we're couting unique customers who ordered product A or B, customers who ordered both will be counted twice in total.

Let me know if you have any suggestion and have a good day.

Regards

 

[lazyvba]

Hello,
Regarding my pivot code, a lot of visits but few of feedback.
I do hope it's working for most of you.

< some text deleted - mod >

Again, if not working, don't hesitate to leave feedback.

Regards

Lazyvba

 

[PaddyD]

Hi Lazyvba,

However well intentioned, solicitations for other people's businesses aren't allowed

 

[DataAnalyst]

I found doing this manually not too bad. Unfortunately I wasn't able to look at LazyVBA's code because my employer blocks personal blogs.

I did a pivot with the dept. name as first field, customer number as 2nd field, and a count field of customer number that shows how many instances of the customer number.

Copied the pivot values and formatting to a new sheet
Selected all the customer numbers, not including total fields
Looked at the bottom right corner of the screen where Excel shows "Count: (the number of cells selected)". This gives the number of unique values.

For customer names, a pivot with the Last Name, then First Name.
Copied the pivot values to a new sheet
A blank row under a name indicates a second instance of the same name. Deleted these blank rows
Selected the remaining names and looked at the bottom right count.

For the customer number summary I was able to enhance it by using filter on the count field and selecting to see how many customers had 1 visit, 2 visits, 3, etc. Using this method could work on fairly large datasets if you use the CTRL+SHIFT+down arrow to select the column quickly. However, deleting the blank rows from a Last Name First Name report could be cumbersome.

I know this may seem obvious to some of you advanced users, but it wasn't to me on the Monday after 4 days off in a row. I agree this should be an integral pivot table function. In fact, I assumed it was and had to re-do a report I had already given my boss. It looked like it was counting unique values but it wasn't.

This is reassurance that computers will never take over the world...

 

[drexgreen]

See Pivot Table - Count unique values - Excel 2010 - Stack Overflow

 

[jkreynard]

See Pivot Table - Count unique values - Excel 2010 - Stack Overflow

Thank you! This is the solution that I used.