Route planning ++

Hey all,

I'm wondering if I can utilize Excel to do some route planning automatically. So here's my "plan"..

1) Every day I get a list of randomly chosen shops (ca. 150 shops)

2) This is then sorted out to 5-7 different routes to drive them

Currently, I'm doing this manually and sorting it by cut and paste.

So my question is; is it possible to "import" a text file for instance and make it sort it autmatically?

The routes are always driven in the same direction, if Shop X isn't on the route, you skip it and go to Shop Y or Shop Z.

Out of the 150 shops there's usually around 100 shops getting deliveries every day, so one day shop X is on the route, and the next day not. And there's no real constant frequency they get deliveries. But the order they are driven is constant.

Also, in the end I need it to be in one A4-page sorted and formatted.

OK! Getting there. :-)

Forgot to add, the list I get every day with the shops getting deliveries is alphabetized if that matters.

So the spreadsheet has to know that "Shop X" goes on "Route Y" and where in the delivery order that shop has to be.

As you can see from the example "Shop 5" on monday is delivered before "Shop 4" on monday. But on tuesday "Shop 4" isn't there, so it goes straight from "Shop 5" to "Shop 6" which is the natural road to take.

I get that I need to define how the routes are going to be by writing them up as a reference for some function in Excel. Just can't wrap my head around how to start... :-)

Route_Example.xlsx

Update with the constant routes defined:

Route_Example_v2.xlsx

In G4, array confirmed with Shift Ctrl Enter

Formula:

Please Login or Register  to view this content.

Then drag / fill the rest of the table.

I didn't notice your location, you would need to use

Formula:

Please Login or Register  to view this content.

To array confirm, you enter the formula into the cell as normal, but hold down Shift and Ctrl while you press Enter.

Ah! I could enter the formula now at least.. and I got the clamps, so I know it was entered as an array.. did some reading about it.

Route_Example_v3.xlsx

"#NAVN?" means "#NAME?" - what am I doing wrong?

Hopefully this will help

Route_Example_v2(with formula).xlsx

Great stuff! Thanks for the help guys!

I've got it to work, and I'm starting to build up the routes and do some formatting now. So far it's working great! But alas I still have some questions.. as I'm building up the routes I'm adding new shops as they show up on the list I get every day.

My question is this, is it possible to add some kind of failsafe, say for example I get a list from my customer on monday. Shops 1 thru 10 is on the list, the spreadsheet processes the information and makes the routes.

BUT there's one shop (Shop #4) in this case which isn't on my "route-guide", but it's on the list for delivery (this could be that it's a new shop we haven't driven to yet or something like that). Is it possible to get the shop to be either highlighted somehow or some "alert" of some kind. It could also be a row with "Not on any routes" and the shops not on any routes listed there, if it's a lot of shops.

Kind of a scrip that checks that ALL the shops has been assigned on a route to put it simpler. :-)

See my example.
Route_Example_failsafe.xlsx

Something like this maybe? Route_Example_v2(No Route).xlsx You could apply conditional formatting as well if desired.

Exactly like that! I'm gonna make a cell with a general alert, but I'll try to figure out that on my own whit the code you gave me!

Thanks!

@ tk240381

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks, didn't find the "Thank"-button.. and I didn't think rep was the same. :-)

Main problem is now solved, but I still have a few kinks to work out, but I'm trying myself first. Guess I can still use this thread even if it's solved?

Yep, if you find you need to continue in this thread just mark it as "unsolved" (as explained in my previous post) and continue on.