Pivot Table to Not Count Duplicates, But Show Duplicates When You Click Into It

[legendary_popsicle]

In short, I would like a pivot table to only count unique values, but when I click into the pivot I would like to show all instances of that value. For example:

I have a table of data that I am creating a pivot table from. There are fields for Customer ID, Task Name, Age, and Notes. There will be multiple records for a single Customer ID each time it has new notes.

I would like to create a pivot table that has Task Name in the Row Labels, Age in the Column Labels, and count of Customer ID in the Values, so that, for example, I can see how many accounts have been in the Design task for 2 days. However, when I do this it counts each record, but I would like it to count each unique Customer ID. Also, when I click into the pivot, instead of pulling up one line per Customer ID, I would like it to pull up each instance of Customer IDs in that Task Name/Age combination (similar to doing a DISTINCT in SQL).

I am probably asking for more than a pivot table is capable of, so any suggestions are greatly appreciated... Thanks in advance!

 

[legendary_popsicle]

Anybody?

 

[legendary_popsicle]

Ok, so for those that may Google this, I have figured it out. You need to have your Customer IDs (or whatever the equivalent is in your report) sorted small to large. Assuming your Customer ID is in column B, then you can have a formula in cell A2 that identifies dupe Customer IDs:

=IF(B2=B1,0,1)

This will assign a 1 to the first instance, and then a 0 to every other instance. Then in your values section of the pivot, put Sum of Dupe ID -- it will give you the count of uniques you need for visual purposes, but when you click into it, it will pull up every instance of that Customer ID.

If anyone knows of a better way to do this, or a way to do it without having to sort the Customer ID column, please do chime in.

 

[JaneR]

Thank you for this suggestion. I have been looking for this for months. I used a variation of this formula, and used 3 columns (A,B,C) to the left of my pivot table. I used the following formulas :
Column A : =+C2+B2
Column B: =IF(D2=D1,0,1)
Column C: =IF(D3=D2,0,1)

Then I used a filter on Column A, and using filter, selected only rows with "0" or "1". This showed me visually every instance of a duplicate in my pivot table.
That was Fun!