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

[zitephur]

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.

[bebo021999]

As per my understanding:
* C column is sorted
* The earliest date (C4) must be chosen to shipped in 1st priority.
* The others (C5:C11) will be compared with C4, if they are within X days, make group with C4 to shipped together
* The 2nd shipment will be start from next group

For example, If X=5 days,
* C4, C5, C6 (21,24,26 Feb) belong to shippment 1 starts on 21 feb
* C7,C8 (27 feb and 2 march) belong to shipment 2 starts on 27 feb

Does it correct? Can we start with this ?

[zitephur]

Yes, and to continue with that example C9 is on its own while C10,C11 can be sent on 3/14/12.

[Alf]

Perhaps if you changed the layout a bit (as a matrix) it would be easier to see which data fits in the 0 to 5 range.

Alf

[zitephur]

Alf, that does make it much easier to see the values of the date differences. Is there a way to solve the problem of automatically grouping with a previous delivery rather then a later one? This looks like the matrix would have to be created manually (the column and row headers), could it be automatically generated? My goal is to have the employee enter the dates and the answers pop out already grouped. Great take on the problem though, thank you.

[Alf]

Ok so it looks like the matrix approach could be a possible solution to your problem. We just need to see if we could sort out some problems first.

Could you set up a matrix the way you want it and give a bit more explanation on how data should be added?

Is there a way to solve the problem of automatically grouping with a previous delivery rather then a later one?

Could you please explain this in more detail as I'm not sure I do understand this properly.

Alf

[zitephur]

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.

A shipment cannot be delayed to send with a later date because the customer will be out of product, however it can be sent with an earlier delivery. A sticky point is how to differentiate between grouping with an earlier delivery because the matrix just gives days difference between all the dates. Need a constraint that it can be sent only with an earlier delivery, never a later one.