Excel VBA Combinations/Permutations

[andrew_sampson]

Hi Guys,

I'm really struggling to get the answer to the following question and hope you can help.

I have a list with items 1, 2 and 3.

How can I create a list that gives the following results: 1, 2, 3, 12, 13, 23 & 123

I know I can just use 3 For loops but I want the code to be able to cope with x numbers in the list. For example 1, 2, 3, 4 and 5 or 1, 2, 3, 4, 5, 6, 7, 8 and 9

Any help would be greatly appreciated

 

[dorinu]

Hey guys,

I would realy appreciate any help regarding my issue, as I am strugalling for days to find a solution.

I want to obtain a list of all possible permutations for a set of values (set of numbers or set of letters) which should contain only unique items.

For example for the set 1, 2 and 3 would be 6 possibilities:

123
132
213
231
312
321

I would need to be able to generate this permutation for a various number of items in the set (although I think Excel limits me to 9, as 10! is already 3.628.800).

Would it be any solution for this?

Many thanks!

Dorin

 

[shg]

Welcome to the board.

UDF?

Row\Col	A​	B​	C​	D​
1​	ABCD	ABCD		B1:B25: {=StrPermute(A1)}
2​		ABDC
3​		ACBD
4​		ACDB
5​		ADBC
6​		ADCB
7​		BACD
8​		BADC
9​		BCAD
10​		BCDA
11​		BDAC
12​		BDCA
13​		CABD
14​		CADB
15​		CBAD
16​		CBDA
17​		CDAB
18​		CDBA
19​		DABC
20​		DACB
21​		DBAC
22​		DBCA
23​		DCAB
24​		DCBA
25​		#N/A​

Function StrPermute(sInp As String) As Variant
  Dim asOut()       As String

  If Len(sInp) > 9 Then
    StrPermute = "Too long!"
  Else
    ReDim asOut(1 To WorksheetFunction.Fact(Len(sInp)), 1 To 1)
    GetPermutation "", sInp, asOut, 0
    StrPermute = asOut
  End If
End Function

Sub GetPermutation(sL As String, sR As String, asOut() As String, nOut As Long)
  ' adapted from http://spreadsheetpage.com/index.php/site/tip/generating_permutations/
  ' source of algorithm unknown

  Dim i             As Integer
  Dim j             As Integer

  j = Len(sR)
  If j <= 1 Then
    nOut = nOut + 1
    asOut(nOut, 1) = sL & sR
  Else
    For i = 1 To j
      GetPermutation sL & Mid(sR, i, 1), Left(sR, i - 1) & Right(sR, j - i), asOut, nOut
    Next
  End If
End Sub

 

[dorinu]

Thank you shg for this fast reply!

So far I was using macros for these kind of issues. I've copied the function's text in the VBA Editor but now I'm not sure how to use it in order to generate the text from the range B1: B25. I tried entering the function in the cell D1 and it returns only one value in that particular cell. Could you please help me with this?

Welcome to the board.

UDF?

Row\Col	A​	B​	C​	D​
1​	ABCD	ABCD		B1:B25: {=StrPermute(A1)}
2​		ABDC
3​		ACBD
4​		ACDB
5​		ADBC
6​		ADCB
7​		BACD
8​		BADC
9​		BCAD
10​		BCDA
11​		BDAC
12​		BDCA
13​		CABD
14​		CADB
15​		CBAD
16​		CBDA
17​		CDAB
18​		CDBA
19​		DABC
20​		DACB
21​		DBAC
22​		DBCA
23​		DCAB
24​		DCBA
25​		#N/A​

<tbody>
</tbody>

Function StrPermute(sInp As String) As Variant
  Dim asOut()       As String

  If Len(sInp) > 9 Then
    StrPermute = "Too long!"
  Else
    ReDim asOut(1 To WorksheetFunction.Fact(Len(sInp)), 1 To 1)
    GetPermutation "", sInp, asOut, 0
    StrPermute = asOut
  End If
End Function

Sub GetPermutation(sL As String, sR As String, asOut() As String, nOut As Long)
  ' adapted from http://spreadsheetpage.com/index.php/site/tip/generating_permutations/
  ' source of algorithm unknown

  Dim i             As Integer
  Dim j             As Integer

  j = Len(sR)
  If j <= 1 Then
    nOut = nOut + 1
    asOut(nOut, 1) = sL & sR
  Else
    For i = 1 To j
      GetPermutation sL & Mid(sR, i, 1), Left(sR, i - 1) & Right(sR, j - i), asOut, nOut
    Next
  End If
End Sub

 

[wiz329]

This is an example of the same code writing the permutations to an array and only writing to the worksheet at the end.

It's much faster. As one could guess, the more elements you have the more relevant is the time of the calculation of the permutations.

For 9 elements I get a total of 986409 permutations. With the code I posted before I got about 65s, with the new abount 25s (15s algorithm + 10s writing and array 986409*9)


Remark: I just quickly teaked the code I posted before. When I have the time I will look again into this to see if I can make it faster.

Hi there -- I was wondering if there were any other easy optimizations to make before I run this code. I am looking to generate all possible subsets up to length 10 from a set of 20 elements (and possibly more in the future), so speed will be important.

Also, I will be using these combinations to a form a database. I'm concerned about the performance of Excel under these conditions (600k - 1.5M rows and 30 columns), so I'm looking to put these in an actual database. Since this needs to be portable, I was considering storing the information in an Access database. Since I'm going to be putting this information in an access database anyways, would it be faster to just write the resulting arrays to a text file for importing to Access rather than writing to cells in Excel?

Thanks in advance,

Austin

EDIT: Also, do you happen to have any any previous posts you may have made or other resources regarding array operations and code optimization. As I mentioned above, there will be ~20-30 rows or calculations associated with each unique combination, and I would like to also speed up the calculation of these characteristics if possible.

 

[wiz329]

Also, if you wouldn't mind posting the code for combinations (rather than permutations), and writing it all at the end, I would greatly appreciate it. I'm not sure how to calculate the lresult for combinations.

 

[cosmo123]

This is an awesome place, I'm learning a lot from studying what people post here. Thank you for your work!

I am working on a little project that requires me to generate all permutations of items from 5 categories (identified in column B as cat1 - cat5). The items will not be in any particular order (as seen below). Once a permutation is generated the values associated with it (value1 - value4) are calculated as simple sums of the values of the 5 components.

Could anyone help with a way to generate all those permutations with items taken from each of the 5 categories? I'd really appreciate any suggestions. Thank you.

category

value1

value2

value3

value4

value1

value2

value3

value4

name 1.1

cat1

5

1

3

4

name 1.1

name 2.1

name 3.1

name 4.1

name 5.1

29

12

21

16

name 4.3

cat4

name 1.1

name 2.1

name 3.1

name 4.1

name 5.2

21

14

22

21

name 2.3

cat2

name 2.1

cat2

11

5

7

9

name 5.4

cat5

name 1.3

cat1

name 3.1

cat3

1

0

8

2

name 3.2

cat3

name 3.3

cat3

name 4.1

cat4

0

1

3

1

name 4.2

cat4

name 5.2

cat5

4

7

1

5

name 5.1

cat5

12

5

0

0

name 1.2

cat1

name 5.3

cat5

name 2.2

cat2

<tbody>
</tbody>

<link rel="File-List" href="<a href=" file:="" d:="" documents="" desktop="" perm_files="" filelist.xml"="" target="_blank"><link id="shLink" href="<a href=" file:="" d:="" documents="" desktop="" perm_files="" sheet001.htm"="" target="_blank"><link id="shLink">

 

[oriwitt]

I linked back from this thread to here http://www.mrexcel.com/forum/excel-...aring-excel-arrays-golf-draw-over-5-days.html already, as the concepts seem similar.

I'm wondering if the code posted here in this thread can be used to check for unique sets of a certain number of elements?

 

[mikerickson]

Oriwitt, it would be best if you started a thread stating your problem.

The concepts used in this thread probably do have application to your specific problem, but a new thread stating your problem clearly would be the best way to get responses.

 

[oriwitt]

No problem, I was offering opinions in both threads and thought the concepts are related.

 

[MaxTrax]

Hi Guys,

I posted this yesterday but had no replies so thought I might try here as it seems similar to what I’m requesting (hopefully).

I have a set of 58 pairs (I’m using paired numbers here but they could be anything. (Eg. Names).

I am hoping someone can help in providing a VBA macro or formula to complete the following:

The result should give a unique random set of *9 pairs* in any order with of course no repeats of pairs.

Eg 1: This result would be correct. 5/10, 20/30, 110/140, 70/ 80, 150/180, 260/270, 40/50, 200/230, 190/220 (A set of 9 unique pairs)

Eg 2: This result would be incorrect. 30/60, 40/50, 40/70 (as both contain 40), 140/170, 5/20, 5/30 (as both contain 5), 100/110, 20/30, 250/280 (Not a set of 9 unique pairs)

It would be excellent if the result could be delimitated with a comma and each number in the pair separated by a forward slash. /

The correct formula will be copied over many cells to give a new random set of 9 in each. It is quite possible some *new sets of 9* in different cells would repeat and that is ok.

Below are the 58 pairs.

Thanks for any help as my VBA knowledge is very limited.

MaxTrax

	A	B	C	D	E	F
1	5	10
2	5	20
3	5	30
4	10	20
5	10	40
6	20	30
7	20	50
8	30	60
9	40	50
10	40	70
11	50	60
12	50	80
13	60	90
14	70	80
15	70	100
16	80	90
17	80	110
18	90	120
19	100	110
20	100	130
21	110	120
22	110	140
23	120	150
24	130	140
25	130	160
26	140	150
27	140	170
28	150	180
29	160	170
30	160	190
31	170	180
32	170	200
33	180	210
34	190	200
35	190	220
36	200	210
37	200	230
38	210	240
39	220	230
40	220	250
41	230	240
42	230	260
43	240	270
44	250	260
45	250	280
46	260	270
47	260	290
48	270	300
49	280	290
50	280	310
51	290	300
52	290	320
53	300	330
54	310	320
55	310	340
56	320	340
57	320	330
58	330	360
59
60
62

<tbody>
</tbody>