pivot table with unique count

[it's an excel world]

Hello all. I know you've probably answered this question a thousand times but when searching I had a hard time finding exactly what I was looking for...
I have a spreadsheet with dates indicated in Column A and order number indicated in Column B. What I need to do is create a pivot table that displays the date with how many different orders shipped that day. Below is an example of my sheet.

Date Order No
1/1/09 123
1/1/09 123
1/1/09 124
1/2/09 123
1/2/09 125
1/2/09 126
1/2/09 126
This is what I would like to see in my pivot table

Date Order No
1/1/09 2
1/2/09 3

I only want each order number counted once within the date range.

thank you!

 

[adept]

Maybe Subtotal would work for you.

IMHO Craig

 

[Dave Patton]

In an adjacent empty column that is next to your data, add a header with a name such as "Helper".
In the first row of that Column , enter a formula like

=SUMPRODUCT(--($A$2:A2=A2),--($B$2:B2=B2))=1

Fill the formula down to the end of your data.
Include the new column in your Pivot Table.

If you prefer, Hide the "False" column.

 

[texasalynn]

try this

Excel Workbook
	A	B	C
1	Date	Order No	Unique
2	1/1/2009	123	1
3	1/1/2009	123	0
4	1/1/2009	124	1
5	1/2/2009	123	1
6	1/2/2009	125	1
7	1/2/2009	126	1
8	1/2/2009	126	0
Sheet2

HTH

edit: too slow, just a little different than Dave Patton's

 

[jim may]

Without A Pivot Table (since I can't figure how to do it, LOL)

Your Data including Header in A1:B8
In Cell C2 enter: (and Commit using Ctr+Shift+Enter)
and Copy down...
watch for wrapping of below..


=IF(AND(SUM(--(FREQUENCY(IF($A$2:$A$8=A2,MATCH($B$2:$B$8,$B$2:$B$8,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$8))))>0))<>C1,$A1<>$A2),SUM(--(FREQUENCY(IF($A$2:$A$8=A2,MATCH($B$2:$B$8,$B$2:$B$8,0)),ROW(INDIRECT("1:"&ROWS($B$2:$B$8))))>0)),"")

 

[Fazza]

This thread seems similar to one earlier in the week http://www.mrexcel.com/forum/showthread.php?t=376471. The other thread shows a pivot table solution and also an approach by adding extra columns without using array formulas (they can carry a lot of overhead sometimes). Another way would be a query table with the same SQL as mentioned for the pivot table.

HTH, Fazza