Counting unique entries in a Pivot Table of based on 50k rows...

[EvilGenius007]

So I have some data that looks something like this:

Bob     Pencil          Writing Utensils
Bob     Pen             Writing Utensils
Chris   Pen             Writing Utensils
Adam    Pen             Writing Utensils
Chris   Legal Pad       Paper
Chris   3-ring binder   Paper
Bob     Pen             Writing Utensils
Chris   Legal Pad       Paper
Chris   Pencil          Writing Utensils
Adam    Pen             Writing Utensils
Chris   Legal Pad       Paper
Chris   Legal Pad       Paper
Bob     Pen             Writing Utensils
Bob     Pen             Writing Utensils
Adam    3-ring binder   Paper

I need a pivot table that looks like:

Writing Utensils
                Pen     3
                Pencil  2
Total Writing Utensils  3
Paper
        Legal Pad       1
        3-ring binder   2
Total Paper             2

Because 3 unique people have Pens, 2 have pencils, all 3 have a writing utensil, and similarly for paper only 1 person has a legal pad (or 4), etc.

Of course, the actual data is about 50k rows, and the "Pen" values and "Writing Utensils" are found via Vlookup on a product name (i.e. "Bic A7849"=>"Pen", "Mead FFF3"=>"Legal pad")...

 

[GlennUK]

You are asking for 2 different things in your requested PivotTable ... unique counts for item, and also unique count for category. You could easily see the results in 2 PivotTables. You'll need 2 extra columns in your data to mark the unique rows. Have a look here:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J
1	Name	Item	Category	Unique Flag	Unique Cat Flag	*	Unique Flag	0	*	Page filter for unique items
2	Bob	Pencil	Writing Utensils	0	0	*	*	*	*	*
3	Bob	Pen	Writing Utensils	0	1	*	Count of Item	*	*	*
4	Chris	Pen	Writing Utensils	0	0	*	Category	Item	Total	*
5	Adam	Pen	Writing Utensils	0	0	*	Writing Utensils	Pen	3	*
6	Chris	Legal Pad	Paper	0	0	*	*	Pencil	2	*
7	Chris	3-ring binder	Paper	0	1	*	Paper	3-ring binder	2	*
8	Bob	Pen	Writing Utensils	1	2	*	*	Legal Pad	1	*
9	Chris	Legal Pad	Paper	1	2	*	*	*	*	*
10	Chris	Pencil	Writing Utensils	0	1	*	*	*	*	*
11	Adam	Pen	Writing Utensils	1	1	*	*	*	*	*
12	Chris	Legal Pad	Paper	2	3	*	Unique Cat Flag	0	*	Page filter for unique items
13	Chris	Legal Pad	Paper	3	4	*	*	*	*	*
14	Bob	Pen	Writing Utensils	2	3	*	Count of Category	*	*	*
15	Bob	Pen	Writing Utensils	3	4	*	Category	Total	*	*
16	Adam	3-ring binder	Paper	0	0	*	Writing Utensils	3	*	*
17	*	*	*	*	*	*	Paper	2	*	*
Sheet10

 

[EvilGenius007]

Thanks.! The "COUNTIFS" has me pointed in the right direction. I'm still hashing out the exact details, but I'll follow up with what I end up actually doing.

 

[GlennUK]

That's great. In case you can't quite see what I'm doing in the PivotTables, I'm using the COUNTIFS result in the Page Field and selecting to view only the 0 ( zero countifs result ) ... as they are the ones that will give you what you need. Good luck!

 

[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 advantage is that this is adaptable to most pivot tables, and recalculate according to filters, row and columns fields.
Unfortunatly, it won't calculate the totals for a category


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

regards