conditional pivot table???

[laandmc]

I have got a problem where I need to work out how many deliveries were made
in a month of sales. I am doing this by if the postcode and the date of
delivery are the same on two separate orders they have gone together on one
van and it is counted as one delivery.


Example

POSTCODE DATE POSTCODE ORDERS DELIVERYS
A 1 A 2 1
A 1 B 2 2
B 1 C 4 2
B 2
C 3
C 1
C 3
C 3

I want to get the second table from the first table, I have tried using
pivot tables but not got very far. Can anyone help?

Please note that in teh date column there is a date not a number, I just put
a number here to make it a bit easier to understand

Cheers

 

[Max]

Assume source table (your 1st table) in cols A and B, data from row2 down
Create a helper col, label it in C1 as: DELIVERY
Put in C2:
=IF(B2="","",IF(SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))>1,"",SUMPRODUCT((A$2:A2=A2)*(B$2:B2=B2))))
Copy C2 down to last row of source data

Then create a pivot on cols A to C, with:
POSTCODE placed in ROW area,
DATE placed in DATA area (as Count of DATE), and
DELIVERY also placed in DATA area (as Sum of DELIVERY)

In the pivot sheet, just drag "Data" and drop it over "Total", to get the
exact results that you seek (your 2nd table). Dress up the pivot's col labels
to taste (you can enter a single trailing space if you hit the prompt that
the pivot fieldname already exists)
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:17,800 Files:359 Subscribers:55
xdemechanik