Results 1 to 7 of 7

How to group (optimize) shipments that are within X days of each other

Threaded View

  1. #1
    Registered User
    Join Date
    02-20-2012
    Location
    CA
    MS-Off Ver
    Excel 2010
    Posts
    5

    Question How to group (optimize) shipments that are within X days of each other

    I've been trying to get my head around this for a few days now, even bought 4 books the thickness of telephone directories but I still have not been able to crack this. I looked at solver which I think may be able to solve this, but I have not been able to figure out how to do it properly. Hope somebody might have an idea or two of how to go about doing this.

    Software:
    Excel 2010

    Problem:

    I have a list of dates that are spread through out the month. I would like to group together dates that are within X number of days from each other, with priority going to grouping with an earlier date rather then a later date if a date is within X days of two dates. For example, dates of 02/10/12, 02/15/12 and 02/19/12 with an X of 5. The middle date of 02/15/12 should be grouped with 02/10/12, and then 02/19/12 sent separately even though the middle date is within 5 days of both dates. If there was a 4th date of 02/22/12, it could be sent with the 02/19/12 date since it is within 5 days. The dates above would all be for one customer, another customer would have their own date range spread over a month that I would also try to consolidate with respect to their shipments.

    What I am Trying to Do:

    I would have a list of about 10 dates that would need to be grouped this way so I can minimize shipments. Rather then send each individual package separately, grouping them with respect to X would help me save a lot of money on shipping, and as a new business every dollar saved helps me survive a day longer. The X can change between different customers, so while some may have an X of 5, others may have an X of 3.

    I would like for the results to be output on a separate part of the sheet so the employee just has to look at the dates and knows exactly what to package together and ship, example, Item Name 1 and Item Name 2 on Date 02/10/15, Item 3 on Date 02/20/12. My ultimate goal is to export this result to .csv file and upload to Google Calendar so that it makes it even easier for the employee to know when to ship.

    Is this even possible with Excel? It seems like anything can be done in Excel, I haven't been able to find an answer to this problem yet but I hope Excel can tackle it.

    If my description is lacking please let me know and I will try to explain more. I have attached a workbook with additional example dates.
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1