SOLVED

permutation and combination

Brass Contributor

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

3 Replies

@yugal40 

See the attached version. You'll have to allow macros.

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.
best response confirmed by yugal40 (Brass Contributor)
Solution

@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
1 best response

Accepted Solutions
best response confirmed by yugal40 (Brass Contributor)
Solution

@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

View solution in original post