create a permutation list

[valuechained]

Hi guys,

I hope you can help me with a particularly tricky problem that I encountered this morning.

I have several items that I would like to combine, lets call them characters A, B, C, D, E

Each character is situated in a different column, next to each other. Below, I would like to create a list of all possible combinations of these characters. The sequence is not important and each row should contain a unique combination of characters.

I have counted 31 combinations for the ABCDE set, namely

1 for ABCDE
5 for A, B, C, D, E each
5 for choosing 5 unique sets of four from a set of five (5!/(4!*(5-4)!)
10 for choosing 10 unique sets of three from a set of five (5!/(3!*(5-3)!)
10 for choosing 10 unique sets of two from a set of five (5!/(2!*(5-2)!)

To complicate matters, I have several other character strings where I would like a list of combinations. For example A, B, C, D, E, F, G, H 9 (a longer set).

By the way, each combination should be spread accross several columns (e.g. one colum per character, see the question marks in the table below)

Any advice would be very much appreciated. This makes my head spin since a couple of hours.

question.xls
	A	B	C	D	E	F
3	Category	A	B	C	D	E
4	Permutation1	?	?	?	?	?
5	Permutation2	?	?	?	?	?
6	Permutation3	?	?	?	?	?
7	Permutation4	?	?	?	?	?
8	Permutation5	?	?	?	?	?
9	Permutation6	?	?	?	?	?
10	Permutation7	?	?	?	?	?
11	Permutation8	?	?	?	?	?
12	Permutation9	?	?	?	?	?
13	Permutation10	?	?	?	?	?
14	Permutation11	?	?	?	?	?
15	Permutation12	?	?	?	?	?
16	Permutation13	?	?	?	?	?
17	Permutation14	?	?	?	?	?
18	Permutation15	?	?	?	?	?
19	Permutation16	?	?	?	?	?
20	Permutation17	?	?	?	?	?
21	Permutation18	?	?	?	?	?
22	Permutation19	?	?	?	?	?
23	Permutation20	?	?	?	?	?
24	Permutation21	?	?	?	?	?
25	Permutation22	?	?	?	?	?
26	Permutation23	?	?	?	?	?
27	Permutation24	?	?	?	?	?
28	Permutation25	?	?	?	?	?
29	Permutation26	?	?	?	?	?
30	Permutation27	?	?	?	?	?
31	Permutation28	?	?	?	?	?
32	Permutation29	?	?	?	?	?
Sheet1

 

[mikerickson]

To replace your list of items, base array should be adjusted.
As a demo, this section of code

ReDim baseRRay(1 To numObjects)
For i = 1 To numObjects
    baseRRay(i) = Chr(64 + i)
Next i

set it to the Array("A","B","...)

You could replace that with an explicit

baseRRay = Array("firstItem","secondItem",...)

Your problem with 28 overflowing the sheet is common. Listing all the possibilites gets really really big very fast.

Why are you listing these posibilites? Analysis would take less space than that big list of possibilites.

 

[Henrique]

To replace your list of items, base array should be adjusted.
As a demo, this section of code

ReDim baseRRay(1 To numObjects)
For i = 1 To numObjects
    baseRRay(i) = Chr(64 + i)
Next i

set it to the Array("A","B","...)

You could replace that with an explicit

baseRRay = Array("firstItem","secondItem",...)

Your problem with 28 overflowing the sheet is common. Listing all the possibilites gets really really big very fast.

Why are you listing these posibilites? Analysis would take less space than that big list of possibilites.

==========================

Thank you for the feedback and question Mike. By the way, it was your message (with the code attached), that made me finally signup and ask the question.

To answer your question, I first must say that early today my brain went on a mini vacation, probably due to lack of sleep... I had this brilliant idea to design all possible variations that 28 variables can take (ranging from 1 to all 28 variables). I am researching some of the variables (factors) that will attract business to a certain location/area.

I little after I posted the question and during an errand, my brain came back and I realized how "silly" my question was, due to the enormity of the number of combinations.

Thanks again and I am happy for making some of you laugh today! Take care,

Henrique

 

[superbeast]

Okay I have a related question. I am trying to generate a list of permutations that go say 1-20 not repeating any numbers in a column. So a proper answer can be say; 1,10,15,20,19,17,6,7,3,2,5,12,11,9,13,14,16,4,8,18 but not be; 1,2,5,1,7,20,19,20,11,10,4,6,9,11,3,6,7,415,17. This has been a real problem that I have been trying to solve for some time and have as yet been unsuccessful.

 

[Eric Golf]

Hi,

Have you read the rest of this thread? It is probably not too difficult to produce what you want but the big question is what are you going to do with the results and where are you going to keep them.

As far as I remember there would be factorial 20 results i.e 2,432,902,008,176,640,000

Eric

 

[mikerickson]

Superbeast, If your problem is to generate the list of all permutations of 1,2,..,20, then you want excel to generate 20! results. Doable, but HUGE!!

Or

Is your problem to generate N different permutations of 1,2,...,20, where N is small (< 5,000 )?