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.

Terminal location	total cost
Mississauga ON	6952
Edmonton ON
Winnipeg MB
Burnaby BC
Flat Rock MI
Chicago IL
Nashville TN
Memphis TN
Atlanta GA
Location 10
Location 11
Location 12
Location 13
Location 14
Location 15
Location 16
Location 17
Location 18
Location 19
Location 20
Grand Total	$ 6952

<TBODY>
</TBODY>

 

[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.