Count unique items in a pivot table

[Blunder1]

Hi,

I need to count the number of unique items using a pivot table in excel 2003. From looking around this does not seem possible. I cannot use formulas as the sheet is too big ( 750mb and 1.5mm rows so i cant undo the whole pivot to count!)

The layout of the pivot will be two fields in the row, then i need a count of unique item in the data field. Is this possible?

Thanks in advance

Blunder

 

[RoryA]

No. Sounds to me like you need to put your data into a database and then use a SELECT DISTINCT query on it.

 

[Blunder1]

shame.

Ok, thank you Rory

 

[RoryA]

Yep. Still can't do it in 2010, in spite of many, many requests. (can do it in PowerPivot though)

 

[GlennUK]

Debra Dalgleish says this:

http://www.contextures.com/xlpivot07.html#Unique

 

[lazyvba]

Hello,

From excel 2007, you can use this code to have unique count within pivot table:
http://lazyvba.blogspot.com/2010/11/improve-your-pivot-table-to-count.html

I do admit it won't help Blunder1, but could help in other cases.

Regards

 

[RoryA]

Interesting - 10 posts here, all being basically just that link.

 

[Fazza]

If you're after a specific result and don't need all manner of other outputs from the pivot table it can be done. Create the pivot table in a file separate from the data taking the external data choice at the first step of the pivot table wizard, ALT-D-P. Easier if you first give the source data a normal (non-dynamic) named range. Follow the wizard and at the last step choose the option to edit in MS Query. Within MS Query edit the SQL to suit, then the 'open door' icon to exit MS Query & complete the pivot table. The resultant worksheet can be moved into the source data file if you like. If you post some sample input data & expected results I can help with the SQL.

Or instead of a pivot table, a query table might be OK?

Maybe you can reduce the file size - if you need to - by not saving the data with the pivot table. Refer PT options.

HTH

PS if you search there will be a number of old posts where I've answered similar questions. cheers

 

[Fazza]

Similar enough?

http://www.mrexcel.com/forum/showthread.php?t=376471

http://www.mrexcel.com/forum/showthread.php?t=319331