Excel Help--Don't know exactly how to, worried about feasibility.

[Bartender]

Hi,
Calling me a programmer of any sort would be a gross exaggeration.

After selecting a list of ten (or more or less, though I can put a lower and upper cap on the number in the list) numbers from an excel spreadsheet, I need to take EVERY possible combination of these numbers (choosing each number only once in each combination) and run through a program which then compares the sum of index values on the excel spreadsheet.
The issue I'm having is the actual generation of each combination of the numbers. I have a column of ten cells, and I need to generate each possible combination (yes, all ten factorial). I will be comparing the sums, and choosing the lowest total sum. Each transition has a value associated with it, so the transition 1->2 might be less than 3->4 or 1->4.
To be clear, I have a column of ten cells which correspond to a table in the spreadsheet.
As for the comparing the sums, I think that is simple:

ii = Factorial(Val(NumVals.Text))
LeastVal=1000000000
For ii = 1 to ii
CurrentVal = Function(...)
‘??? can't figure out how to generate each combination. I am thinking a whole new function which generates the sequence and the interrogates the spreadsheet.
If  CurrentVal < LeastVal Then
LeastVal = CurrentVal
LeastNum1 = CurrentNumA ‘etc.
Next ii
Else
Next ii
End If

I know for a fact that the sums will be lower than, say, a billion, so I set the first least # to that so it will be instantly replaced. I store the information about the best combo so far in the 'Least' series variables, and replace it if I find a new best combination. Any ideas on what my function should look like?
(I have already coded in the simple factorial function that I call.)

Also, I am concerned that my program might take, say, forever to run. I don't have enough practical experience to know whether this is a valid concern or not, in this case, but 10! = 3.63 million or so. And if my list is longer, that number goes way up...

Thanks in advance for any advice,
Bartender

[Ecniv]

Every possible combination... and create it in a spreadsheet?
you only have 65535 rows...

Also I tried something similar for the Lotto numbers, it failed , but only had a couple of duplicates in 14 million numbers. Still took most of a day to store each combination (with no duplicate numbers in the 6 choices) and that was to an mdb (600mb after it finished... or was it larger... not sure...)

[Bartender]

I'm sorry, either I misspoke or mis-coded, or you misunderstand:
I am only storing one combination at a time. After each combination is generated and referenced, it is compared to the current best combination. If it is better, it replaces the one that is stored, and if it is worse, it is, in effect, thrown away. It's just that it has to iterate factorial(the number of values input) times.
Does this make sense, or am I missing something?

[Ecniv]

Ah ok .. well storage wise not a problem.

Time however may be. I suggest you run for say 10,000 or 100,000 loops and time it once you have it, to give you a rough idea of how long it will take.

[Bartender]

I can do that, no problem. My problem is, I don't know how to get the function working. I intended to use the iteration value to generate each combination in order, but I ran into a roadblock, both with trying to obtain -in sequence- every combination of the 10 numbers, and with trying to reference back to the spreadsheet though I probably can figure the reference part out.

Also, I thought of something that might be important: the numbers could repeat in the spreadsheet cells. In other words, I might have repeat numbers in my combinations, though no number in a given cell may repeat.
If my numbers are 1,2,3,4,5,6,7,8,9,10, then this is not an issue, but if my numbers are 2,5,15,20,2,3,4,7,8, then the number 2 will be used twice.

Thanks again for any advice.

[Ecniv]

You need to have a loop and counter upwards from a number. When you reach the max number, it resets the previous number up one and sets itself to one up on that number.

eg:
Starts
1,2,3,4,5,6
1,2,3,4,5,7
1,2,3,4,5,8
etc
1,2,3,4,5,49
1,2,3,4,6,7

Try coding a loop like that and see what happens.

[Bartender]

Sorry, I'm having trouble coding that counter. It seems to me it will have to go through 10 to the 10th power iterations to finish, even if for some of the iterations it just discards and moves on? It needs to never repeat the numbers, but it still has to go through sequences like 1,2,3,4,7,5,6....1,2,3,4,7,6,5... etc.
Like I said, I'm a very new coder, so I might not see a trick to make it work efficiently.

[Bartender]

Maybe I'm thinking counter-productively, and you can correct me, but I was thinking the sequence 1,2,3,4,5,6,7,8,9,0 would continue:
1,2,3,4,5,6,7,8,0,9
1,2,3,4,5,6,7,9,8,0
1,2,3,4,5,6,7,9,0,8
1,2,3,4,5,6,7,0,8,9
1,2,3,4,5,6,7,0,9,8
etc.
And I have no idea how to do that.

[Ecniv]

So you want to have the ten numbers (0-9) and re-arrange them into all different combinations of those numbers?

hmmmmmm

First thought would be to start with one number, and move it through different positions, then move two numbers, then three numbers... But I don't think that would give you all the combinations.

How would you do it if you were doing it manually with say 4 or 5 numbers?
Make up a flow chart how you decided where to put the numbers to give you all the combinations.
Then convert that flow chart to code.

Another way would be an incremental count (cascading back through each position) with checks to make sure that the numbers are not repeated. Then if they are not repeated, you check against the last held total/combination and do the checks you want to see if it gives bigger/better result. Time consuming, but would probably work. Perhaps this is the initial way forward, then once working (so you can demonstrate it) then you can persue other routes?

Or you can end up...