Making Excel List combinations

[Karlsen]

I am trying to program excel to list all possible combinations for 14 Items, The combinations only use ten of those items and must allow repeating. I know that this is a massive amount of data. nCr= ((n+r)-1)!/(r!)(n-1)!,
14C10=((14+10)-1))!/(10!)(14-1)!= 1,144,066 possible combinations. I want excel to display these combinations for me. For example I have 14 types of food
Apples (A), Bananas (B), Carrots (C), Dates (D), Eggplants (E), Figs (F), Grapes (G), Ham (H), Ice (I), Jam (J), Kiwis (K), Limes (L), Mangoes (M), and Nectarines (N) of which only ten are needed in each combination.

So in Column A, I want the choices A1=(A), A2= (B), A3= (C) and so on. In Column C I want each cell to hold the possible combination For example, Cell C1 could look like (A,A,B,C,C,D,E,F,G,H).

Any help you could give me in how to write this in VBA would be greatly appreciated.

 

[hiker95]

Karlsen,

Welcome to the MrExcel forum.

See if this gets you started in the right direction:

ListPermutationsOrCombinations
Myrna Larson, July 25, 2000, Microsoft.Public.Excel.Misc
http://www.mydatabasesupport.com/forums/spreadsheets/250560-combinations.html

 

[hiker95]

Karlsen,


If worksheet Sheet1 looks like this:

Excel Workbook
	A
1	C
2	10
3	A
4	B
5	C
6	D
7	E
8	F
9	G
10	H
11	I
12	J
13	K
14	L
15	M
16	N
17
Sheet1

The link to the ListPermutationsOrCombinations macro, will produce 1,001 rows like this in another worksheet:

Excel Workbook
	A
1	A, B, C, D, E, F, G, H, I, J
2	A, B, C, D, E, F, G, H, I, K
3	A, B, C, D, E, F, G, H, I, L
4	A, B, C, D, E, F, G, H, I, M
5	A, B, C, D, E, F, G, H, I, N
6	A, B, C, D, E, F, G, H, J, K
7	A, B, C, D, E, F, G, H, J, L
8	A, B, C, D, E, F, G, H, J, M
9	A, B, C, D, E, F, G, H, J, N
10	A, B, C, D, E, F, G, H, K, L
11	A, B, C, D, E, F, G, H, K, M
12	A, B, C, D, E, F, G, H, K, N
13	A, B, C, D, E, F, G, H, L, M
14	A, B, C, D, E, F, G, H, L, N
15	A, B, C, D, E, F, G, H, M, N
16	A, B, C, D, E, F, G, I, J, K
Sheet3