| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with please..
Hi all
I have 20k rows representing a whole year of activity of delivering into 5 DC's. What I need to do is get a figure of how many of these DC's got there delivery's on time. Now, drivers may visit the same DC more than once a day, and may have multiple customer's goods per visit. This translates as thus. Month | Driver | Customer | DC | Time Due | Time In Jul Jack ABC HDC 10:00 09:30 Jul Jack DEF HDC 10:00 09:30 Jul Jack XYZ HDC 10:00 09:30 The Customer information is not important, what I need is to compress these 3 lines of data into 1 row so that I can determine if Jack delivered into the DC on time which would register a 1, else it's a zero. The columns in question are this. Set wksTarget = Sheets("Jan") Set rngMonth = Sheets("Data").Range("$A$2:$A$20000") Set rngDC = Sheets("Data").Range("$H$2:$H$20000") Set rngDriver = Sheets("Data").Range("$L$2:$L$20000") Set rngDCD = Sheets("Data").Range("$N$2:$N$20000") Set rngDCI = Sheets("Data").Range("$O$2:$O$20000") The target sheet listed above is set to "Jan" as I am not sure whether or no to break the entire 20K rows into individual months, then graph them from their respective sheets. Essentially, a driver can have as many as 5 deliveries a day, of which he may visit up to 3 DC's so there will be multiple sets of data to add to the multiple rows ( representing the non-important individual customers goods they carry into the DC ). So, the end result I am hoping to achieve is this: Month | DC | Total Deliveries | On Time | % Jul HDC 250 200 80.00 Jul LDC 200 100 50.00 Jul NDC 750 700 93.33 Jul RDC 150 145 96.67 So the whole thing would be 12 individual months of this result. Any assistance is appreciated. TIA Mick. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help with please..
Mick
I think you just need to add an additional column headed by, "on time". That would contain a formula, =if(time due<time in, 1, 0). Copy that formula down fro all 20k rows of data, then make a pivot table. You will be able to to exactly what you want, and a whole lot more by simple manipulating the pivot table. I hope this helps. Ken On Monday, July 23, 2012 5:45:25 AM UTC-4, Vacuum Sealed wrote: Hi all I have 20k rows representing a whole year of activity of delivering into 5 DC's. What I need to do is get a figure of how many of these DC's got there delivery's on time. Now, drivers may visit the same DC more than once a day, and may have multiple customer's goods per visit. This translates as thus. Month | Driver | Customer | DC | Time Due | Time In Jul Jack ABC HDC 10:00 09:30 Jul Jack DEF HDC 10:00 09:30 Jul Jack XYZ HDC 10:00 09:30 The Customer information is not important, what I need is to compress these 3 lines of data into 1 row so that I can determine if Jack delivered into the DC on time which would register a 1, else it's a zero. The columns in question are this. Set wksTarget = Sheets("Jan") Set rngMonth = Sheets("Data").Range("$A$2:$A$20000 ") Set rngDC = Sheets("Data").Range("$H$2:$H$20000 ") Set rngDriver = Sheets("Data").Range("$L$2:$L$20000 ") Set rngDCD = Sheets("Data").Range("$N$2:$N$20000 ") Set rngDCI = Sheets("Data").Range("$O$2:$O$20000 ") The target sheet listed above is set to "Jan" as I am not sure whether or no to break the entire 20K rows into individual months, then graph them from their respective sheets. Essentially, a driver can have as many as 5 deliveries a day, of which he may visit up to 3 DC's so there will be multiple sets of data to add to the multiple rows ( representing the non-important individual customers goods they carry into the DC ). So, the end result I am hoping to achieve is this: Month | DC | Total Deliveries | On Time | % Jul HDC 250 200 80.00 Jul LDC 200 100 50.00 Jul NDC 750 700 93.33 Jul RDC 150 145 96.67 So the whole thing would be 12 individual months of this result. Any assistance is appreciated. TIA Mick. |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Need help with please..
|
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
