Sorting Based on From/To Scenario

[Jim Judkins]

I have a table ("array") that is used to determine
changeover times depending on material (based on
a "From/To" scenario, where the difficulty of change is
indicated by a number from 1 through 5 ("1" being the
easiest/fastest, and "5" being the most
difficult/slowest). For example, if I am running material
A and I subsequently have to run materials B, C, & D, this
will help determine the most efficient order (based on
changeover time) that the products should be run.
So far I have been able to extract the changeover value (1
through 5) based on input of "From" and "To" information
(i.e.: what materials are being changed from/to).
My question is this: How can I structure the spreadsheet
so that I can sort the various potential options into the
most efficient order? For example, if the changeover
times are:
A to B = 5
A to C = 5
A to D = 4
B to C = 1
B to D = 5
D to B = 5
D to C = 5
then it would be most efficient to changeover as follows:
A to D = 4
D to B = 5
B to C = 1
Total changeover = 10,
and least efficient (1/3 longer) to changeover like this:
A to B = 5
B to D = 5
D to C = 5
Total changeover = 15.
Again, how can I sort to yield the most efficient
combination of "From/To"?

 

[Geof Carrington]

-----Original Message-----
I have a table ("array") that is used to determine
changeover times depending on material (based on
a "From/To" scenario, where the difficulty of change is
indicated by a number from 1 through 5 ("1" being the
easiest/fastest, and "5" being the most
difficult/slowest). For example, if I am running material
A and I subsequently have to run materials B, C, & D, this
will help determine the most efficient order (based on
changeover time) that the products should be run.
So far I have been able to extract the changeover value (1
through 5) based on input of "From" and "To" information
(i.e.: what materials are being changed from/to).
My question is this: How can I structure the spreadsheet
so that I can sort the various potential options into the
most efficient order? For example, if the changeover
times are:
A to B = 5
A to C = 5
A to D = 4
B to C = 1
B to D = 5
D to B = 5
D to C = 5
then it would be most efficient to changeover as follows:
A to D = 4
D to B = 5
B to C = 1
Total changeover = 10,
and least efficient (1/3 longer) to changeover like this:
A to B = 5
B to D = 5
D to C = 5
Total changeover = 15.
Again, how can I sort to yield the most efficient
combination of "From/To"?
.
Can't be specific without some research but have you read

up "goal seek" in help?