How to find all combinations of a range of numbers

[Kawaz23]

Hi All
I have 12 numbers and i want to find all possible combinations of those numbers. Could someone tell me the formula please.

eg 2,8,15,17,18,24,28,33,34,35,40,41. How do i find all possible combinations of these numbers in excel.

Thanks kawaz

 

[Erdinç E. Karaçam]

Hi there,

For 12 combinations we need totally: 479.001.600 rows.

But Excel (2^16) = 65.536 rows.
Excel 12: (2^20) = 1.048.576 rows.

We can make permitation for 8 character on a sheet with the codes below.

But we can make it if we edit the codes. Need to seperate per 65.537. rows to a new column. But i didn't used to try for this:

Check out the codes iy you interested:

Run Metin_Girisi to characters between 2-8.

Dim SiradakiSatir

Sub Metin_Girisi()

    Dim Metin As String

        Metin = InputBox("Permütasyonunu istediğiniz metni giriniz...:")
            
            If Len(Metin) >= 8 Or Len(Metin) < 2 Then
                    Msg = "2 karakterden küçük metinin permütasyonu zaten olmaz bu biiiiir... " + Chr(10)
                    Msg = Msg + Chr(10) + "Ayrıca...  9 Karekter için        362.880 !" + Chr(10)
                    Msg = Msg + Chr(10) + "Ayrıca... 10 Karekter için    3.628.800 !" + Chr(10)
                    Msg = Msg + Chr(10) + "Ayrıca... 11 Karekter için   39.916.800 !" + Chr(10)
                    Msg = Msg + Chr(10) + "Ayrıca... 12 Karekter için 479.001.600 ! satır lazım....O yüzden 8 karakter bizim için yeterli..." + Chr(10)
                    MsgBox Msg
                Exit Sub
            Else
                ActiveSheet.Columns(1).Clear
                    SiradakiSatir = 1
                Call PermutasyonHesapla("", Metin)
            End If
    
End Sub

Sub PermutasyonHesapla(e As String, y As String)

    Dim i As Integer, j As Integer
    
        j = Len(y)
        
            If j < 2 Then
                Cells(SiradakiSatir, 1) = e & y
                SiradakiSatir = SiradakiSatir + 1
            Else
                
                For i = 1 To j
                    Call PermutasyonHesapla(e + Mid(y, i, 1), Left(y, i - 1) + Right(y, j - i))
                Next i
                
            End If
    
End Sub

Note:

Need to 12 characters permitations: 479.001.600 cells

For Excel 2003;
65.536 rows * 256 columns = 16.777.216 cells

So we need to 29 worksheets for full result.

 

[Kawaz23]

Erdinç E. Karaçam

Thanks for the reply but as a novice i dont have a clue what to do with that formula.

 

[Erdinç E. Karaçam]

Hi,

Open Excel and press ALT + F11

Insert | Module

(Automaticly named the module as "Module1")

Copy this codes to Module1

Then Return to Excel and press ALT + F8:

From the macro list: run Metin_Girisi Macro.

Write to InputBox an example. (2 - 8 character maximum.)

And From A1 cell, codes will be listing the permitations of the inputed data.