# hello, generate all the formations of the 3 letters .

#### piter

##### Active Member
hello, generate all the formations of the 3 letters
in the following criterion = no more than 5 letters for each of the 3 lines
a = 01 02 03 04 05 06 07 08 09 1 0
b = 11 12 13 14 15 16 17 18 19 20
c = 21 22 23 24 25 26 27 28 29 30 31
So
the formations will have 7 letters
the maximum repetition of 5 per line
aaaaabc, aaaabbc ............. bbccccc

### Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

#### sandy666

##### Banned - Rules violations
something like this?

part of ... 2187 rows

 letters aaaaaaa aaaaaab aaaaaac aaaaaba aaaaabb aaaaabc aaaaaca aaaaacb aaaaacc aaaabaa aaaabab aaaabac aaaabba aaaabbb aaaabbc aaaabca aaaabcb aaaabcc aaaacaa aaaacab aaaacac aaaacba aaaacbb aaaacbc aaaacca aaaaccb aaaaccc aaabaaa aaabaab aaabaac aaababa aaababb aaababc aaabaca aaabacb aaabacc aaabbaa

Last edited:

#### piter

##### Active Member
hello sandy, no more than 5 letters per line, in your example there are 7
maximum of 5 for each of the lines
the order is increasing =
can not have ex = abbaa = can not
corret = aaaabb
go to bbccccc this is the last training of a maximum of 5 per line

#### sandy666

##### Banned - Rules violations
so I misunderstood, sorry

#### MickG

##### MrExcel MVP
Perhaps:-
Code:
``````[COLOR="Navy"]Sub[/COLOR] MG27Feb02
[COLOR="Navy"]Dim[/COLOR] a, Ray, nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = Array("a", "b", "c")

[COLOR="Navy"]For[/COLOR] n = 0 To UBound(Ray)
[COLOR="Navy"]For[/COLOR] a = 1 To 5
nStr = nStr & Ray(n)
[COLOR="Navy"]Next[/COLOR] a
[COLOR="Navy"]Next[/COLOR] n

[COLOR="Navy"]For[/COLOR] n = 1 To Len(nStr) - 6
c = c + 1
Cells(c, 1) = Mid(nStr, n, 7)
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

#### piter

##### Active Member
aabbccc.......missing permutationscriterion = maximum 5 letters for each line, but has can not have only 9 has more permutations ai!
abbbccc? etc......

#### MickG

##### MrExcel MVP
Try this :-
Data (aaaaabbbbbccccc) in "A1:A15" Results in column "C".
Code:
``````[COLOR="Navy"]Sub[/COLOR] Combinations()
'[COLOR="Green"][B]Re:-Pgc01[/B][/COLOR]
[COLOR="Navy"]Dim[/COLOR] rRng [COLOR="Navy"]As[/COLOR] Range, p
[COLOR="Navy"]Dim[/COLOR] vElements, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant
Set rRng = Range("A1", Range("A1").End(xlDown)) '[COLOR="Green"][B] The set of numbers[/B][/COLOR]
p = 7

vElements = Application.Index(Application.Transpose(rRng), 1, 0)
ReDim vresult(1 To p)
Call CombinationsNP(vElements, CInt(p), vresult, lRow, 1, 1)
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

[COLOR="Navy"]Sub[/COLOR] CombinationsNP(vElements [COLOR="Navy"]As[/COLOR] Variant, p [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] vresult [COLOR="Navy"]As[/COLOR] Variant, lRow [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] iElement [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer,[/COLOR] iIndex [COLOR="Navy"]As[/COLOR] Integer)
[COLOR="Navy"]Dim[/COLOR] i [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]

[COLOR="Navy"]For[/COLOR] i = iElement To UBound(vElements)
vresult(iIndex) = vElements(i)
[COLOR="Navy"]If[/COLOR] iIndex = p [COLOR="Navy"]Then[/COLOR]
lRow = lRow + 1
Range("C" & lRow) = Join(vresult, "")
[COLOR="Navy"]Else[/COLOR]
Call CombinationsNP(vElements, p, vresult, lRow, i + 1, iIndex + 1)
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]Next[/COLOR] i
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]``````
Regards Mick

#### piter

##### Active Member
hello 7 formations, 0000000
15??? not

Replies
7
Views
102
Replies
13
Views
161
Replies
5
Views
113
Replies
1
Views
71
Replies
1
Views
191

1,195,619
Messages
6,010,733
Members
441,567
Latest member
Flitbee

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back