Pivot Table Question

[carl]

I have a table with 2 Columns and 300000 Rows.

ColA ColB
May 5 BOX12
May 5 BOX10
Jun 2 BOX05
Jun 2 BOX05
Jun 3 BOX112

Can a Pivot Table Return the following ?

Date Count Unique Products
May 5 2
Jun 2 1
Jun 3 1

Thank you advance.

 

[Debra Dalgleish]

I think there's an extra zero in your number of rows!

To obtain a unique count in a pivot table, you'd have to add a column to
the source table. Peo Sjoblom provided a link to a solution when you
posted this question a couple of days ago:


http://groups.google.com/groups?&[email protected]

 

[Guest]

Thank you. I looked at the link and could not understand
how the example can be adapted to my situation. Do you
have a suggestion ?

 

[Debra Dalgleish]

Assuming you have headings (Date, Code) in row 1 --

In cell C1, enter a heading, e.g. Unique
In C2, enter:

=IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1)
Copy the formula down to the last row of data
Create a pivot table with Date in the row area, and Unique (as a Sum),
in the Data area

 

[carl]

Thank you. This worked well on a few cells.

When I try and run it on the whole sheet it seems to stall
out.

Is there a way to enable it to run on 30 to 60 thousand
rows ?

Thanks Again.

 

[Debra Dalgleish]

If you could compare each row to a smaller range, the formula would
calculate faster. For example, if the list is sorted by date, and
there's a maximum of ten entries per date:


=IF(SUMPRODUCT((OFFSET(A2,-MIN(ROW()-2,9),0,MIN(ROW()-1,10),1)=A2)*(OFFSET(B2,-MIN(ROW()-2,9),0,MIN(ROW()-1,10),1)=B2))>1,0,1)