Pivot Tables - Count Unique

[Optimesh]

Hi All!

Let's say I have a customer dataset with lots of data on each individual customer's past acquisitions.
Let's say I have a unique identifier for each customer, the month of purchase, store location, the item purchased and other data as well, I can "throw" to the Pivot Table (or filter by it).

Suppose I want to build a PT that shows:
Row labels: Store Location
Column Labels: Month of year
Filter: item=shirt
Values: Count of Customer Unique Identifiers
HOWEVER, if a customer bought more than 1 shirt, I want to count him just once! I mean, I want to PT to show count of unique customers that bought a shirt.
for simplicity, let's also say that each customer can only go to one location.
also, let's say I only care about the month of the 1st purchase.

How do I count uniques in a PT ?

Ironically perhaps, this is the one thing I found I'm able to do with a google spreadsheet and not in excel...

Your help will be greatly appreciated

 

[Optimesh]

Well, I think I found a work around.
I used the method described here:
Count Unique Items in Excel Pivot Table

Is there a "more elegant" way to go about it?
Is there a way to do it without adding another column, but right from within the pivot table?

Cheers

Hi All!

Let's say I have a customer dataset with lots of data on each individual customer's past acquisitions.
Let's say I have a unique identifier for each customer, the month of purchase, store location, the item purchased and other data as well, I can "throw" to the Pivot Table (or filter by it).

Suppose I want to build a PT that shows:
Row labels: Store Location
Column Labels: Month of year
Filter: item=shirt
Values: Count of Customer Unique Identifiers
HOWEVER, if a customer bought more than 1 shirt, I want to count him just once! I mean, I want to PT to show count of unique customers that bought a shirt.
for simplicity, let's also say that each customer can only go to one location.
also, let's say I only care about the month of the 1st purchase.

How do I count uniques in a PT ?

Ironically perhaps, this is the one thing I found I'm able to do with a google spreadsheet and not in excel...

Your help will be greatly appreciated

 

[Fazza]

hi,

By using SQL to modify the dataset the specific goal can be achieved: though please note, this won't give you a the full pivot table functionality as if you'd created a normal pivot table straight from the data.

I've posted many similar times in the past so will look for a similar thread to save me re-typing the details. The SQL for this specific question would be like (adjust for your exact headers) below.

HTH

SELECT DISTINCT [customer ID], [store location], [month of purhcase], item
FROM your_source_data

 

[Fazza]

an old thread for info http://www.mrexcel.com/forum/excel-questions/503179-pivot-tables-count-function.html#post2483137

 

[DataAnalyst]

This is a nice method although there seems to be a typo - the formulas should end with )*1, not ,1). I thought it would work until I realized I need a way to count the number of non-blank cells in one of the columns in my pivot table when it is filtered with other criteria and not showing all the data. I need to count only the identifiers showing in one column of the filtered pivot table.

I haven't been able to find a way to do this other than selecting the cells in the column and looking at the count on the bottom right. This works because it doesn't count blank cells (I checked with a manual count).

I'm using a small dataset with one month of data right now. I expect this will work even for a large data set if I use CTRL + down arrow to select the column quickly and easily. But I wish there was a way to do this that's less cumbersome and error-prone.

Thanks everyone for your help. Very interesting reading!

Well, I think I found a work around.
I used the method described here:
Count Unique Items in Excel Pivot Table

Is there a "more elegant" way to go about it?
Is there a way to do it without adding another column, but right from within the pivot table?

Cheers