Need help with please..

  • Thread starter Thread starter Vacuum Sealed
  • Start date Start date
V

Vacuum Sealed

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.
 
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
 
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
Click to expand...
Hi Ken

I had originally done this but it gives me incorrect % figures as it
counts the multiple instances ( by that I mean multiple customers ) of a
driver who is delivering into the DC

I may have to look at a multiple level filtering on the main data page
and copy those rows into the individual months whereby using these as
helper sheets, then copy/condensce each month back into a summary page.

I will keep chipping away at it.

Thanks again.

Cheers
Mick.
 
Back
Top