Permutation or Combination

Vishaal

Active Member
Joined
Mar 16, 2019
Messages
261
Thanks in advance

I want to know that from the given

(Query-1) numbers 0,1,2,3,4,5,6,7
(Query-2) numbers 1,2,3,4,5,6,7,8

how many combination we can generate without repetition and if possible pls provide the generated combination.
 

tyija1995

Well-known Member
Joined
Feb 26, 2019
Messages
648
Hey,

By combinations do you mean 0,1,2,3,4,5,6,7 is 1 combination, 0,1,2,3,4,5,7,6 would be another?

If so then for both queries there are 40320 combinations (8!)
 

trunten

Active Member
Joined
Jul 26, 2011
Messages
478
I have a permute function which i could shoehorn into a solution for you. try:
Code:
Sub combinations()
    Dim numbers, output(), v
    Dim d As Object
    Dim i As Long, j As Long
    Dim s As String
    
    numbers = "1,2,3,4,5,6,7,8"
    numbers = Split(numbers, ",")
    Set d = permute(UBound(numbers) + 1)
    ReDim output(1 To d.count, 1 To 1)
    i = 0
    For Each v In d.items
        i = i + 1
        s = ""
        For j = 1 To UBound(v)
            s = s & IIf(s = "", "", ",") & numbers(v(j) - 1)
        Next j
        output(i, 1) = s
    Next v
    Workbooks.Add.Sheets(1).Range("A1").Resize(UBound(output), UBound(output, 2)).Value = output
    Set d = Nothing
    Erase output
End Sub
    
Public Function permute(n As Integer) As Object
    Dim P() As Integer, permSet() As Integer
    Dim t As Integer, i As Integer, j As Integer, k As Integer
    Dim count As Long
    Dim Last As Boolean
    Dim d As Object
    
    If n <= 1 Then
        Debug.Print "Please give a number greater than 1"
        Exit Function
    End If
    'Initialize
    Set d = CreateObject("Scripting.Dictionary")
    ReDim P(n)
    For i = 1 To n
        P(i) = i
    Next i
    count = 0
    Last = False
    Do While Not Last
        ReDim permSet(n)
        For t = 1 To n
            permSet(t) = P(t)
            'Debug.Print P(t);
        Next
        'Debug.Print
        count = count + 1
        d.Add count, permSet
        Last = True
        i = n - 1
        Do While i > 0
            If P(i) < P(i + 1) Then
                Last = False
                Exit Do
            End If
            i = i - 1
        Loop
        j = i + 1
        k = n
        While j < k
            ' Swap p(j) and p(k)
            t = P(j)
            P(j) = P(k)
            P(k) = t
            j = j + 1
            k = k - 1
        Wend
        j = n
        While P(j) > P(i)
            j = j - 1
        Wend
        j = j + 1
        'Swap p(i) and p(j)
        t = P(i)
        P(i) = P(j)
        P(j) = t
    Loop 'While not last
    Debug.Print "Number of permutations: "; count
    Set permute = d
End Function
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,700
Office Version
2010
Platform
Windows
If you mean combinations, there are 8:

7 6 5 4 3 2 1 0
8 6 5 4 3 2 1 0
8 7 5 4 3 2 1 0
8 7 6 4 3 2 1 0
8 7 6 5 3 2 1 0
8 7 6 5 4 2 1 0
8 7 6 5 4 3 1 0
8 7 6 5 4 3 2 0
8 7 6 5 4 3 2 1
 

Forum statistics

Threads
1,081,417
Messages
5,358,553
Members
400,503
Latest member
RedSquirrel

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top