Nov 19 2021 03:12 AM
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
Nov 19 2021 03:33 AM
See the attached version. You'll have to allow macros.
Nov 19 2021 09:35 PM
Nov 19 2021 11:56 PM
SolutionChange 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
Nov 19 2021 11:56 PM
SolutionChange 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