Combination Help

[eyal8r]

Hey guys-
I have a list of 10 of my favorite/lucky numbers that I want to play in the lottery. The lottery picks 5 numbers total. I need a way to show me all the possible combinations of my 10 numbers picked in a 5 number draw (hope that makes sense). There are no repeat combinations- for example- I DO NOT WANT 1-2-3-4-5 and 5-4-3-2-1 to come up as separate combinations- so each of my favorite #s needs to be used only once in each combination, and each set used once.

I have searched this board for 2 hours now- read tons of other posts, but not finding a real solution. The output will be a list of all the possible combinations (no repeats, and no permutations) using my 10 favorite numbers. Another example-
1-2-3-4-5
1-2-3-4-6
1-2-3-4-7
1-2-3-4-8
1-2-3-4-9
1-2-3-5-6
1-2-3-5-7
and so on.

How do I create this? I realize the resulting table will be quite a large number of combinations- but we're going to have fun with it and pick a few at random. Any help is appreciation!

 

[pgc01]

Hi Stephen

I' ve tried to understand the programme you write before, but some idea can't grasp.

I'm afraid I've written too much. Hope you don't get bored.

I used a recursive code.

The logic used to get all the possible combinations(n, p) was:

- in the first position place any of the elements
- in the remaining positions write the combinations of the remaining elements taken (p-1) at a time.

So in the example you posted (P = 3, velements = ( A,B,C,D,E,F )), the total combinations are

the A followed by the combinations of ( B,C,D,E,F ) in the next 2 positions
the B followed by the combinations of ( C,D,E,F ) in the next 2 positions
the C followed by the combinations of ( D,E,F ) in the next 2 positions
the D followed by the combinations of ( E,F ) in the next 2 positions

This is why the Sub calls itself, because to find the second level of combinations the logic is exactly the same.

When the code executes the first line

"the A followed by the combinations of ( B,C,D,E,F ) in the next 2 positions"

the Sub call itself and the same logic is applied, giving

I - the A followed by B followed by the combinations of ( C,D,E,F ) in the next 1 position
II - the A followed by C followed by the combinations of ( D,E,F ) in the next 1 position
III - the A followed by D followed by the combinations of ( E,F ) in the next 1 position
IV - the A followed by E followed by the combinations of ( F ) in the next 1 position

There are still combinations to calculate, and so, to execute the first line:

I - the A followed by B followed by the combinations of ( C,D,E,F ) in the next 1 position

, the Sub calls itself again, resulting in:

the A followed by B followed by C (no more positions, so no more combinations follow)
the A followed by B followed by D
the A followed by B followed by E
the A followed by B followed by F

This finishes line I, giving the first combinations (A,B,C), (A,B,D), (A,B,E) and (A,B,F). Now the code continues and executes line II:

II - the A followed by C followed by the combinations of ( D,E,F ) in the next 1 position

This results in:

the A followed by C followed by D (no more positions, so no more combinations follow)
the A followed by C followed by E
the A followed by C followed by F

Now line II is finished, giving more combinations (A,C,D), (A,C,E) and (A,C,F). And now the code will execute line III, ... and so on.

The iIndex variable reflects which position you are filling. Now I hope the behaviour of iIndex is clear.

- In your no. 1 instruction, the code is placing the "A" in position 1, iIndex is 1. Then the the Sub calls itsef to resolve the other 2 positions incrementing iIndex.
- In your no. 2 instruction, the code fills position 2 with B, IIndex is 2. Then the the Sub calls itsef to fill the last incrementing iIndex.
- In your instructions nos. 3,4,5,6, the code fills position 3 with C,D,E,F, IIndex is 3.
- Then the Sub ends and returns to where it was called, and that is inside itself, to the instruction "Next i". In your instruction no. 7, since it has returned it's now again in the loop to fill the second position, with iIndex=2. It will now continue the loop, filling position 2 with C and calling itself again to fill position 3 (instruction no. 8), etc.

Each time the Sub call itself, it increments the iIndex. When it returns it gets the previous value of iIndex.

Hope now is clear.

 

[pgc01]

Hi CARBOB

Did you try my code again in a new workbook? Are the response times now the ones expected (for the example you posted less the half a second instead of several minutes)?

 

[CARBOB]

Hi CARBOB

Did you try my code again in a new workbook? Are the response times now the ones expected (for the example you posted less the half a second instead of several minutes)?

pgc01, I started a new book and it works like you said. Thank you. I have no idea how long it took, my old tired eyes can't blink that fast. I have but one question, is it possible to have the macro do either Permutations or Combinations?

 

[pgc01]

CARBOB

I'm glad it's working now how you wanted.

I have but one question, is it possible to have the macro do either Permutations or Combinations?

Do you mean the ones I posted, combinations without repetition and permutations with repetition?

 

[CARBOB]

Be able to enter an P or C in A1, the values starting in A2 and the number of different numbers to generate in B1 (6) without repeats.

Book1
	A	B	C	D
1	P OR C	6	3	3
2	3		3	3
3	2		3	3
4	1		3	3
5	0		3	3
Sheet1

 

[pgc01]

I'm not sure we are talking about the same thing. Both Combinations and Permutations can be with repetition or without repetition. Usually when one speaks about repetition in the context of Combinations/Permutations it means repetition of elements of the set in the Combination/Permutation.

Ex:

1 - Combinations without repetition:
I'm going to send 3 people to the next excel course (3 people to the same course). The department has 10 people. How many combinations are there? (each person can only be chosen once)

I posted this one.

2 - Combinations with repetition:
I'd like to have 4 children. How many gender combinations are there? Ex: GGGG (4 girls) or GGBB (2 boys and 2 girls). Here the elements (G,B) can appear more than once in the combination.

I did not post this one.

3 - Permutations without repetition:
I'm going to send 3 people to the next office course (1 word, 1 excel and 1 access). The department has 10 people. How many permutations are there? (the courses run at the same time and so one person cannot be chosen twice)

I did not post this one.

4 - Permutations with repetition:
How many numbers with 3 odd digits are there? Here 133 is allowed, the 3 can appear more than once.

I posted this one.



I hope this clarifies what I meant.

The combinations/permutations will be generated without duplicates, meaning that each combinations/permutation appears just once on the list.

 

[CARBOB]

I understand now, thank you very much.

 

[pgc01]

Anyway, if you want to have just one code that merges the 2 codes I posted, it's very easy, because the 2 codes I posted have only 1 small difference:

For the combinations without repetition:

For i = iElement To UBound(vElements)

for the permutations with repetition:

For i = 1 To UBound(vElements)

So you just have to add a parameter to the second sub to decide the initial value of the loop variable.

 

[Stephen_Tsui]

Hi Pgc01:

Thx your reply.

I know your logic, but still don't know why in step 7, iindex reduce 1, from 3 to 2. In your programme, there is no "-" symbol, why the variable i and iindex can have a decrease?

Can you indicate which line of Code cause this reduction, thx.

 

[Stephen_Tsui]

Hi Pgc01:

Thx your reply.

I know your logic, but still don't know why in step 7, iindex reduce 1, from 3 to 2. In your programme, there is no "-" symbol, why the variable i and iindex can have a decrease?

Can you indicate which line of Code cause this reduction, thx.