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

 

[mountainclimber11]

1) Do you care about the probability of each number's occurrence? In other words, would each number have the same likelihood as others of occurring? If so, create a unique value list of your values using a unique filter in Excel. If not, leave it as-is.

2) Can the pairs be different from what you have listed? For example can a good pair be 330/10?

 

[amryasser]

No, each number can have its number of occurring depending on the permutation and no the pairs are just what I have listed

 

[mountainclimber11]

Off the top of my head, I would do a random number between 1 and the number of pairs you want to pick from (so between 1 and 58 in your sample/question) using this:
https://www.techonthenet.com/excel/formulas/rnd.php

Create a do while loop that does that until you have count of "good" pairs (9) you want. In that loop I use the rnd link above to generate a number (1 to 58). Use that number to index the corresponding pair in you list. Concatenate it using some like this = rng.offset(0,1) & "/" & rng.offset(0,2) (assuming rng is the range of you index 1 to 58). Before you store it in a "good" list in the spreadsheet check that it doesn't already exist using rng.Find(..etc...). If it doesn't exist add it to your list of "good" pairs and increase an increment variable by 1. If the pair already exists on your "good" list, don't increment and skip putting it on your "good" list and loop again to find another rnd number/pair index (1 to 58) and corresponding range (rng) and pair.

Try to write code for that and I'll try to give you some pointers. Sorry, I am working and can't do it all for you.

 

[vanthinh3101]

Hi Andrew
Welcome to the board

You want to calculate the Power Set of a Set.

You can try this code:

Option Explicit
 
' PGC Oct 2007
' Calculates a Power Set
' Set in A1, down. Result in C1, down and accross. Clears C:Z.
Sub PowerSet()
Dim vElements As Variant, vresult As Variant
Dim lRow As Long, i As Long
 
vElements = Application.Transpose(Range("A1", Range("A1").End(xlDown)))
Columns("C:Z").Clear
 
lRow = 1
For i = 1 To UBound(vElements)
    ReDim vresult(1 To i)
    Call CombinationsNP(vElements, i, vresult, lRow, 1, 1)
Next i
End Sub
 
Sub CombinationsNP(vElements As Variant, p As Long, vresult As Variant, lRow As Long, iElement As Integer, iIndex As Integer)
Dim i As Long
 
For i = iElement To UBound(vElements)
    vresult(iIndex) = vElements(i)
    If iIndex = p Then
        lRow = lRow + 1
        Range("C" & lRow).Resize(, p) = vresult
    Else
        Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
    End If
Next i
End Sub

Test:

- Write a, b, c, d in A1:A4
- run PowerSet

	A	B	C	D	E	F	G
1	a
2	b		a
3	c		b
4	d		c
5			d
6			a	b
7			a	c
8			a	d
9			b	c
10			b	d
11			c	d
12			a	b	c
13			a	b	d
14			a	c	d
15			b	c	d
16			a	b	c	d
17
[Book1]Sheet1

<tbody>
</tbody>

Dear Mr. PGC01,
I'm a new member.
I has a question which is familiar with this post and hope you can help.
I have a list with items 1, 2, 3, 4, ..., 10.
How can I create a list that gives the following results: 1 -2 , 1 - 3, 1-4, 1-2-3, 2-3-4... (each number is in a cell). The result's condition is the total is less than 10. The condition will be taken from a inputbox.
I'm look forward to hearing from you.
Best regard.

 

[almrib]

I need a macro that is almost theses, I've numbers and I need the permutation in a specific amount, for example, the list of numbers is 1 to 25, and i need permutate theses numbers in group of 14 numbers, all of then, (1-2-3-4-5-6-7-8-9-10-11-12-13-14), (1-2-3-4-5-6-7-8-9-10-11-12-13-15), (1-2-3-4-5-6-7-8-9-10-11-12-13-16)...
I appreciated the help.

 

[saumyar]

Hi All,

I am new user and I have some similar requirement. I need permutation of A, B, C and D but I also need to specify maximum how many times each letter can occur.
i.e I need A to appear max 4 times, B 2 times, C 2 time and D 2 times. Valid examples AAAA, AAAB, AABB, AACC, CCDD (since these are within limits). Invalid examples ABBB, ACCC, ADDD, CCC, CCCA (because B,C,D appeared more than 2 time)
Is there an easy way to do this? Any help/direction would be appreciated.

<tbody>
</tbody>

 

[mikerickson]

saumyar, This thread is over 2 years old.
You would get better, faster responses if you started you own thread about your problem specifically.

 

[saumyar]

Sure. Thanks

https://www.mrexcel.com/forum/excel-questions/1052112-excel-alphabet-permutations-limitations.html