finding the N! routes in excel

[mutu]

let say i have six cities.
A-B-C-D-E-F

a guy in city A, must visit all other cities and come back to A..

ABCDEFA
ABCDFEA
ABCEDFA
ABCEFDA
ABCFDEA
ABCFEDA

so that this gives us 120 route.

is it possible in Excel..

[Dav]

Combin, permut and fact all exist as excel functions, your answer will be using these, to give you a number!

If you want all the other routes listed you would have to have a macro written to do it, but 120 is a reasonable number to do this for. But from the post I just think you want the number.

Regards

Dav

[mutu]

i just not want number. i need routes... thanks a lot though

[Dav]

The attached sheet lists them. the macro to do it is contained as well, it is a functional macro, not a pretty one, and im sure the iterations could be reduced if I thought about it a bit more

Regards

Dav

[mutu]

tkanks a lot Dav. now i need to think how this work... can you tell me a little what i need to change say 7or 5 city?

[Dav]

I do not have the code in front of me but

well for 6 cities you need 5 loops as the stat and finish at the sixth

for 5 cities you would need to loose a loop and for 7 cities add a loop
you also need to write the information for the extra citiesout in the cells statement and make the condition that selects solutions (countifs of each value in the range) be extented by one. My logic is that if a solution is done, the value will only match with itsself in the countif so for 6 cities (5 variables) this will equal 5, if you cahnged it so 5 cities it will equal 4

Hopefully you can see the pattern

Regards

Dav

[mutu]

Thanks Dav. i got you. i will play with it a little

[mutu]

hey Dav, hope you read this.

i did what you have told and i got what i what.
now i need to add distances between cities. i must add this distance matrix to the VB so that i can find minimum total distance between cities.

A B C D E F G
A - 10 5 7 ..........
B
C
D
E....

is it possible to do that?

[Dav]

There could be a tie to the minimum distance ansi assume you want the route as well. This is just adding the distance to ach route that fits the criteria. sorting it would give you the minimum routes

Hope that helps

Regards

Dav

[mutu]

I really appreciate all of you guys for helping me.
i bookmarked and will always visit the forum. There are a lot of helpful formulas and so.


Thanks A Lot