Pivot Tabel Question.

[GEM]

I have the following example,

Date Item
9/2/2008 194
9/2/2008 194
9/2/2008 889396
9/2/2008 889396
9/3/2008 331
9/3/2008 331
9/3/2008 178

I created a pivot table to count items by date. But what I need is to
consolidate items. For example,

Item 194 appears two times on 9/2/2008, my pivot table is counting it as 2,
what I need is to consolidate the item, if the same item (194) appears twice
in a day, to show it as one. So on my pivot I would like to have the answer 2
on 9/2/2008, because two unique numbers (194 & 889396) appear on that date.

Can anyone help???

 

[AltaEgo]

When creating you pivot table:
- Drag 'Date' to the Row fields area
- Drag 'Item' to the Data items area
- ALSO, drag 'Item' onto the Column or Row fields area.
- Right click Sum of Item and change to Count

 

[Debra Dalgleish]

There's no function in a pivot table that will count unique items. As a
workaround, you could add a field to the source data, to calculate the
count of operators, and add that field to the pivot table. There's an
example here:

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

 

[GEM]

I still can't get unique numbers?? Is there any way around this??

 

[Ted M H]

A couple of big If's:

If you are using Excel 2007 and
If you are able to mess with the source data for your Pivot Table

You can do this:

Select the Date and Item columns and choose Data > Remove Duplicates
This will give you a list like this:

Date Item
9/2/2008 194
9/2/2008 889396
9/3/2008 331
9/3/2008 178

Now build the Pivot Table on this reduced source data and you'll get this:

Date Count of Item
9/2/2008 2
9/3/2008 2

I think you can also get to unique items in Excel 2003 by using Advanced
Filters, but if my solution doesn't work for you your best best will be
Debra's.

 

[AltaEgo]

Sorry, I misread your question earlier.

You can count unique outside the pivot if it is set up with date in rows and
items in columns.

Remove totals from your pivot.
Outside the pivot on the R/H side perform a count of the pivot item counts.

Example, Using your data, I set the formula =COUNT(B5:E5) in E6 which was
just outside the pivot table.

If refreshing the pivot causes problems, you could write a macro to write a
revised formula in the rows next to the pivot each time it is updated. Or,
use a simple formula and hide everything from column B to the row before
your formula.

 

[GEM]

Thanks to all!!!!!!!!

A couple of big If's:

If you are using Excel 2007 and
If you are able to mess with the source data for your Pivot Table

You can do this:

Select the Date and Item columns and choose Data > Remove Duplicates
This will give you a list like this:

Date Item
9/2/2008 194
9/2/2008 889396
9/3/2008 331
9/3/2008 178

Now build the Pivot Table on this reduced source data and you'll get this:

Date Count of Item
9/2/2008 2
9/3/2008 2

I think you can also get to unique items in Excel 2003 by using Advanced
Filters, but if my solution doesn't work for you your best best will be
Debra's.