Forum Discussion

yugal40's avatar
yugal40
Brass Contributor
Nov 19, 2021
Solved

permutation and combination

Hi Folks,

Hope you all are doing great!
i have numbers in the column a, Now i want to calculate all the permutation and combination of all the pair of 3 cells cells and there should be no repetitions in the same, Like i only need unique results, I am open to VBA and excel functions both.
Waiting for some experts response, From scratch i have given an example in column c.
Thanks

excel
Excel for web
Formulas and Functions
Macros and VBA
Office Scripts
  • HansVogelaar's avatar
    HansVogelaar
    Nov 20, 2021

    yugal40 

    Change the Test macro as follows; the rest can remain the same:

    Sub Test()
    Dim Num As Long
    Dim r As Long
    Dim LastRow As Long
    Num = Application.InputBox(Prompt:="How many items in each combination?", Default:=3, Type:=1)
    If Num < 2 Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.count).End(xlUp).Row
    ReDim myArray(1 To LastRow)
    For r = 1 To LastRow
        myArray(r) = Range("A" & r).Value
    Next r
    Call CombosNoRep(myArray, Num)
    Application.ScreenUpdating = True
End Sub

3 Replies

    • yugal40's avatar
      yugal40
      Brass Contributor
      Nov 20, 2021
      I really appreciate your understanding of macros!

      Really thanks for your reply, It worked for me.

      Just one more thing can we give an input instead of hard coding 3 as a number?

      It will make my task more easier

      Thanks again,
      Have a great day.
      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Nov 20, 2021

        yugal40 

        Change the Test macro as follows; the rest can remain the same:

        Sub Test()
    Dim Num As Long
    Dim r As Long
    Dim LastRow As Long
    Num = Application.InputBox(Prompt:="How many items in each combination?", Default:=3, Type:=1)
    If Num < 2 Then
        Beep
        Exit Sub
    End If
    Application.ScreenUpdating = False
    LastRow = Range("A" & Rows.count).End(xlUp).Row
    ReDim myArray(1 To LastRow)
    For r = 1 To LastRow
        myArray(r) = Range("A" & r).Value
    Next r
    Call CombosNoRep(myArray, Num)
    Application.ScreenUpdating = True
End Sub

Resources