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

 

[shg]

I think this is correct:

      -A- --B--- --C--- --D--- --E--- --F---
  1   Set Univ=2 Univ=3 Univ=4 Univ=5 Univ=6
  2     1      2      3      4      5      6
  3     2      3      6     10     15     21
  4     3      4     10     20     35     56
  5     4      5     15     35     70    126
  6     5      6     21     56    126    252
  7     6      7     28     84    210    462

The formula in B2 and copied across and down is

=COMBIN($A2+B$1-1, B$1-1)

The "Univ=" in row 1 is just formatting.

 

[crakonit]

It is correct indeed.

Thank you.

 

[mic_kas]

Hi!

Thanks for the great piece of code it was (almost) exactly what I was looking for! But, (there's always a but ) as I need to create very large power sets (for sets with hundreds of elements) the limited number of rows in Excel become a problem. Furthermore I only need the subsets with two or more elements. I use these subsets to solve Capacitated Vehicle Routing Problems and in order to easily include the sets to my optimization code I would like a specific format on the output:
For the set S{1,2,3}
I would want to get this output:
1.(1,2)
2.(1,3)
3.(2,3)
4.(1,2,3)
Where the first number is a counter for the subsets.

Is there any possibility you could modify the code so that it writes the output to a file, for instance txt, in the above format. I tried to modify the code myself but as I'm very unfamiliar with VBA I didn't get anywhere.

Thanks for your help,
Mic_kas

 

[Guvrok]

Hi there:

Sorry to ask this silly question, i am not really good on VBA coding, i would like to create a sequence of permutation giving in excel at the next horizontal columnes (at the same line) a closed ring of numbers by preserving the native order, exemple: 12345 - 51234 - 45123 - 34512 - 23451, so the whole permutation can be included in the same case in order to apply additional functions bellow.


Thank you for your help.

 

[mikerickson]

IF A1=1, B1=2, C1=3, D1=4, E1=5
put
=IF(COUNTA($1:$1) < COLUMN(A1), "", INDEX($1:$1, 1, 1+MOD(COLUMN(A1)-ROW(A1)-1, COUNTA($1:$1))))

in a cell and drag down and right

 

[Guvrok]

Thank you so much.

An other question, how can i keep the first zero of each number i put without setting the mask to some special mask (like 000000 or 0000) because the numbers length is different from each others (example: 0165895325, 09865842, 048 ...).
i think i need a macro code no?

 

[mikerickson]

I'm not sure what you are asking. If you want an initial character in the result,

=IF(COUNTA($1:$1) < COLUMN(A1), "", "0" & INDEX($1:$1, 1, 1+MOD(COLUMN(A1)-ROW(A1)-1, COUNTA($1:$1))))

If the problem is that you have strings and the formula is converting them to numbers =theformula & "" will fix that.

 

[Guvrok]

The command you gave me previously didn't work with my french excel version even i've translated the commands.
i've used:

=MOD(A1;10)*10^(NBCHAR(A2)-1)+(A2-MOD(A2;10))/10

And then i dragged it;

For the numbers without 0 there is no problem, the problem is when a number contains 0 so in the rotation process, the 0 disappears, so i want to use a command line or a macro to "fix" the zero when it returns at the beginning of the number.

Thank you mikerickson;

 

[dechex]

Hi
Welcome to the board

With the set of elements in A1, down, try:

Sub PowerSetRept()
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 Long, iIndex As Long)
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, iIndex + 1)
    End If
Next i
End Sub

[TABLE="width: 2"]
<tbody>[TR]
[TH][/TH]
[TH="width: 30, align: center"]A[/TH]
[TH="width: 30, align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"]F[/TH]
[/TR]
[TR]
[TD="align: center"]1[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]2[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]3[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]11[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]12[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]13[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]14[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]15[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]16[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]17[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]18[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]19[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]20[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="align: center"]21[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD="colspan: 7"] [Book1]Sheet2[/TD]
[/TR]
</tbody>[/TABLE]

I am looking for something similar:

<tbody>
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="width: 30, align: center"]C[/TH]
[TH="width: 30, align: center"]D[/TH]
[TH="width: 30, align: center"]E[/TH]
[TH="width: 30, align: center"][/TH]

[TD="align: center"] 1 [/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 2 [/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 3 [/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 4 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 5 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 6 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 7 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 8 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]a[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"] 9 [/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]b[/TD]
[TD="align: left"]c[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]a[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"]c[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]c[/TD]
[TD="align: left"]b[/TD]
[TD="align: right"]a[/TD]
[TD="align: right"][/TD]

[TD="align: center"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: left"]...[/TD]
[TD="align: left"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Basically a list of all combinations. Thank you for your help.

 

[pgc01]

Hi

Looking at the example you posted you don't want the list of all the combinations, or else you would not have posted both (a,c) and (c,a) which are the same combination.

I guess you mean a list of all the permutations.

Try my post #2, where I posted the solution to the powerset, replacing the statement:

For i = iElement To UBound(vElements)

with

For i = 1 To UBound(vElements)