Results 1 to 3 of 3

Thread: Unique problem

  1. #1

    Unique problem

    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi all... This is my first post here... and I have come with a unique problem which I didn't know which forum to post in so this one seemed the most appropriate... I am posting it on other excel boards as well... kinda like a shotgun approach since I am short on time.... anyway, below is my query.

    I have very basic knowledge of MS Excel compared to you guys. However, I am very keen to learn and grow my knowledge in Excel so please bear with me for what I have come up with. I would like a solution for this laborious manual work that I have to do for a whole fleet of vehicles.

    - The source file is a monthly movement report for dedicated vehicles making multiple stops at different locations to load and offload products for the company. I have for your convenience included just one vehicle.
    o The CLIENT destinations have been highlighted as Green.
    o Beige/fawn colors are those where if the vehicle stays more than 30 mins, it should be regarded as one of the destinations. (since these are close to Client destinations and most probably have been parked outside)
    o KDC in blue is also essentially a Client destination but for some purposes is highlighted in blue which need not be any cause for concern.

    - Target file is the breakdown of the vehicle’s movement into round trips starting from Karachi and ending back in Karachi (Either FCL-the Plant OR WS – Workshop). This constitutes a round trip.

    - Sample output file is the final report which I generate by manually putting all the dates & times from the source file to the target file.

    Need to automatically extract date and time values against specified repetitive locations in the set output format in the target file. (If it could be bettered, I am open to any input).

    Files included:

    Sample output file
    Source file
    Target file
    P.S. I also want to know how to properly protect my sheets.
    Attached Files Attached Files

  2. #2
    Conjurer WizzardOfOz's Avatar
    Join Date
    Sep 2013
    Excel Version
    Office 365
    Quick question, can the GPS location data (latitude and longitude) also be added to the source file. A street descriptive is likely to get errors whereas lat/long (rounded to say 0.001 ~100m) is more definitive.

    I would treat the Source data as a database and with database queries
    1: Extract only the data for the points of interest.
    2: Generate leaving/arriving times for each point of interest and vehicle
    3: From this list break down into trips (possibly just a pivot table)

    Regarding protection of sheets, it's generally easy to bypass so it only protects from novice users.

  3. #3
    Yes indeed, I had previously requested the geo coordinates, however they do not appear in a separate column but appear in the Location column along with the street descriptive. I shall share that with you as well, however I believe we shall need a set of coordinates or what is called in technical parlance, Geo-Fencing, of a location where if a vehicle is stationary for a specific amount of time, only then the the data should be extracted, since some of these locations are on the highway and sometimes the vehicle will just be passing by hitting the same coordinates as the one which might be reporting there.

    Source file with GeoCoordinates.xlsx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts