permutations

[martyna]

Hi I have a list of 20 terminals with check boxes besides them and upon clicking each box several cost values show up in a table calculating the total costs associated with choosing that terminal. You can choose any combination of the 20 from 1 to 20 and the cost always displays in the same cell so with running each combination I would need to grab the value of that cell and compare it to the last combination.

I need to write a code that will run through all the scenarios and choose the scenario with the lowest cost, then either select those terminals or just have a msg box with the most cost effective choice.

I'm not sure how to code all the permutations as the number of combinations is 20! and doing nested loops will take forever. Unfortunately my vb skills are not advanced enough to tackle this one but I'm sure there is a better way than 20+ nested loops.

If it's not too much to ask I would be curious to know the top 3 or 5 cost effective combinations, but if that's too much just the cheapest is great.

Thank you.
Martyna

 

[Drrellik]

you will probably need to provide more information about the layout or the data in the sheet and the current formula's you are using to calculate your values.

 

[martyna]

you will probably need to provide more information about the layout or the data in the sheet and the current formula's you are using to calculate your values.

Terminal locations are in Column B and there is a check box besides each location (20 of them)
Mississauga ON is row 19. Total Cost is in column M and the grand total is dynamic based on the selection of locations it appears in M39. There are coluns in between with volume, number of shipments and other data specific to selecting each terminal which show up when the terminal is selected as selecting the terminal populates it's name in another sheet so the numbers are calculated there and then a sumif or lookup function makes them appear on this sheet.

the cost changes based on which and how many terminals you select, so I'm looking for a code that will run through 20 permutations/combinations of selection of terminals and for each selection compare the total cost in M39 and then output the cheapest result and the combination.
[TABLE="class: grid, width: 500"]
<TBODY>[TR]
[TD]Terminal location[/TD]
[TD]total cost[/TD]
[/TR]
[TR]
[TD]Mississauga ON[/TD]
[TD]6952[/TD]
[/TR]
[TR]
[TD]Edmonton ON[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Winnipeg MB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Burnaby BC[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Flat Rock MI[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Chicago IL[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Nashville TN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Memphis TN[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Atlanta GA[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 10[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 11[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 12[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 13[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 14[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 15[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 16[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 17[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 18[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 19[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Location 20[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD]$ 6952[/TD]
[/TR]
</TBODY>[/TABLE]

 

[shg]

I'm not sure how to code all the permutations as the number of combinations is 20!

It's 2^20; each terminal can be in or out.

The lowest single price is the cost of the lowest terminal. The lowest combination of two is the two lowest prices.

You need some other criteria.