VBA code for Permutation and combination

Post deleted.

Hi saravanan2021,

Using the PERMUTATIONA function that was released with Excel 2013 you are after 456,976 permutations with repetition...

=PERMUTATIONA(26,4)

...verified via =26^4

So using pgc01's nifty solution from here, this will do the trick for you:

'Original Thread https://www.excelforum.com/excel-programming-vba-macros/1386523-vba-code-for-permutation-and-combination.html
'Based on https://www.mrexcel.com/board/threads/all-possible-combinations-with-repeats-allowed.436932
Option Explicit
 
Dim arrElements As Variant
Dim arrResult As Variant
Dim p As Long
Sub ListPermWithRepeats()

    Dim wsOutput As Worksheet
    Dim lRow As Long
    
    Application.ScreenUpdating = False
    
    Set wsOutput = ThisWorkbook.Sheets("Sheet1") 'Name of sheet for the permutations. Change to suit if needed.
 
    arrElements = Array("A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z")
    p = 4
    ReDim arrResult(1 To (UBound(arrElements) - LBound(arrElements) + 1) ^ p, 1 To 1)
     
    PermutRep 1, "", lRow
    wsOutput.Range("A2").Resize(UBound(arrResult)) = arrResult 'Output from cell A2. Change to suit if needed.
    
    Application.ScreenUpdating = True

End Sub
Sub PermutRep(ByVal lInd As Long, ByVal sresult As String, ByRef lRow As Long)
    
    Dim i As Long
     
    For i = LBound(arrElements) To UBound(arrElements)
        If lInd = p Then
            lRow = lRow + 1
            arrResult(lRow, 1) = sresult & arrElements(i)
        Else
            PermutRep lInd + 1, sresult & arrElements(i), lRow
        End If
    Next i
    
End Sub

Regards,

Robert

See attached: Sheet2

Run this code in a new workbook on sheet1. (Combinations)

Dim ar, sq, x As Long

Sub jec()
 ar = [transpose(char(row(65:90)))]
 ReDim sq(Application.Combin(26, 4), 0)
 x = 0
 
 Application.ScreenUpdating = False
 
 Gen_comb "", -1, 26 - 4
     
 With Sheets(1).Cells(1, 2)
   .CurrentRegion.ClearContents
   .Resize(x) = sq
 End With
End Sub

Sub Gen_comb(xStr As String, y As Long, n As Long)
 Dim sp, j As Long
 For j = y + 1 To n
    sp = xStr & ar(j + 1)
    If Len(sp) = 4 Then
      sq(x, 0) = sp
      x = x + 1
    Else
      Gen_comb xStr & ar(j + 1), j, n + 1
   End If
 Next
End Sub

Permutation is different from combination

Agreed. John Topley's and your suggested solution provides the 14,950 combinations reconciled using =COMBIN(26,4)

But based on the initial request...

Hi experts,
I need a vba code to make all possible combinations of 4 letters from 26 Alphabets (A-Z) in one column.
for e.g. AAAB, AAAC, BARB, ABCL, etc.

...only ABCL is in the output using these solutions so I think (very possibly wrongly) we need to output all the permutations with repetitions.

Yes good call

For permutation, change "C" to "P" in A1 !!!

This for combination of 2 or 3 (change the "c" value in code)
With more combination lettes, add more "for..loop": l,m,n (6 combination), Note that with 6 combination of 26, it could be 381 milion cases and could not paste into a single column.

PHP Code: 

Option Explicit
Sub test()
Dim i&, j&, k&, x&, c&, arr()
c = 3 ' could be 2 or 3
ReDim arr(1 To 26 ^ c, 1 To 1)
For i = 65 To 90
    For j = 65 To 90
        If c = 2 Then
            x = x + 1
            arr(x, 1) = Chr(i) & Chr(j)
            If x = (26 ^ c) Then GoTo Out
            GoTo Outj
        End If
            For k = 65 To 90
                    x = x + 1
                    arr(x, 1) = Chr(i) & Chr(j) & Chr(k)
            Next
Outj:
    Next
Next
Out:
Range("A:A").ClearContents
Range("A1").Resize(UBound(arr), 1).Value = arr
End Sub 


words of 5 characters out of 26 characters

Oh Man,

Awesome!!!!!!!!!!!!

Can I change iNumbers = 5 to iNumbers = 7 to make 7 letter word and so on?

NO you 'll have memory problems while executing the macro because the result for 6 letters is 1.000.000 rows by 166 columns.
I can rewrite the macro, so that you can handle blocks of, for example, 1.000.000 rows by 10 columns, so in 16 steps 'll have all those possibilities.

What 'll be the benefits of having those immense data ?

7 letters = 1.000.000 rows * 3.316 columns
8 letters = almost 4 complete worksheets

i didn't test it and it 'll take a while ...

Solution with a formula

Please try in A1 and copy down 456,976 times:

Formula:

=CHAR(MOD((ROW()-1)/26^3,26)+65)&CHAR(MOD((ROW()-1)/26^2,26)+65)&CHAR(MOD((ROW()-1)/26,26)+65)&CHAR(MOD(ROW()-1,26)+65)

An other possibilty for users Excel 365 and 2021:

Please try in C1 (or in any other cell you like) :

Formula:

=LET(c,SEQUENCE(26^4,,0),CHAR(MOD((c)/26^3,26)+65)&CHAR(MOD((c)/26^2,26)+65)&CHAR(MOD((c)/26,26)+65)&CHAR(MOD((c),26)+65))

@Hans, the question was 6 letters = 26^6 = almost 309 000 000.
that 'll be a problem, i suppose ...

The last question was all 6-letter-words that can be made with the alphabet = 26^6.
Your answer #23 was 26^4, so it's almost the same but 2 gears higher ...
the only question, what 's the benefit of that ???

Put AAAA in A1 and then put
=IF(RIGHT(B1,3)="zzz", IF(MID(B1,1,1)="Z","A",CHAR(CODE(MID(B1,1,1))+1)), LEFT(B1,1)) & IF(RIGHT(B1,2)="ZZ", IF(MID(B1,2,1)="Z","A",CHAR(CODE(MID(B1,2,1))+1)), MID(B1,2,1)) & IF(RIGHT(B1,1)="Z", IF(MID(B1,3,1)="Z","A",CHAR(CODE(MID(B1,3,1))+1)), MID(B1,3,1)) & IF(RIGHT(B1,1)="Z","A",CHAR(CODE(RIGHT(B1,1))+1))

in B1 and drag down

this are the numbers :, i used the wrong numbers in 20.
6 is 309 columns of 1 000 000 cells

"...but it crashes far before."
At least you tried, i wrote the macro and stopped it because it took too long ..

just for the fun: 6 letters = 309 million permutations = 2,11 GB filesize (as predicted by @HansDouwe)in almost 10 minutes
Attachment 794892