Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional pivot table???
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
conditional pivot table???
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 --- "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? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting in a pivot table | Excel Discussion (Misc queries) | |||
Conditional Formatting in Pivot Table | Excel Worksheet Functions | |||
pivot table conditional formatting | Excel Discussion (Misc queries) | |||
conditional formatting in pivot table | Excel Discussion (Misc queries) | |||
Conditional Format and Pivot Table | Excel Discussion (Misc queries) |