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?

Cheers

You can do it with a pivot table but you need to determine how many unique
deiveries you had. To do that I added 2 extra columns to the source data so
that I ended up with this...

Post Code Date Deliveries Unique
A 1.00 A1 0.50
A 1.00 A1 0.50
B 1.00 B1 1.00
B 2.00 B2 1.00
C 3.00 C3 0.33
C 1.00 C1 1.00
C 3.00 C3 0.33
C 3.00 C3 0.33

The formula in C2 is =A2&B2
The formula in D2 is =1/COUNTIF($C$2:$C$9, C2)

Here is the pivot table...
Data
Post Code Count of Date Sum of Unique
A 2 1
B 2 2
C 4 2
Grand Total 8 5

--
HTH...

Jim Thomlinson


"laandmc" wrote:

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?

Cheers

This is great.. however does it work when the date column is a date rather
than a number??


"Jim Thomlinson" wrote:

You can do it with a pivot table but you need to determine how many unique
deiveries you had. To do that I added 2 extra columns to the source data so
that I ended up with this...

Post Code Date Deliveries Unique
A 1.00 A1 0.50
A 1.00 A1 0.50
B 1.00 B1 1.00
B 2.00 B2 1.00
C 3.00 C3 0.33
C 1.00 C1 1.00
C 3.00 C3 0.33
C 3.00 C3 0.33

The formula in C2 is =A2&B2
The formula in D2 is =1/COUNTIF($C$2:$C$9, C2)

Here is the pivot table...
Data
Post Code Count of Date Sum of Unique
A 2 1
B 2 2
C 4 2
Grand Total 8 5

--
HTH...

Jim Thomlinson


"laandmc" wrote:

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?

Cheers