Here's what I want to do. I have 6 numbers (1, 2, 3, 4, 5 and 6) and I
need to break these six numbers into sets of 3 so that each number is
grouped with 2 other unique numbers with no repeat numbers. So, you
can't pair 1,1,1 or 1,12, each number has to be unique. There should
be 120 different sets of numbers, but I can't find a macro or formula
to do this. Any ideas?
Ideally I'd like it to look like this:
1,2,3
1,2,4
1,2,5
1,2,6
1,3,4
1,3,5...and so on.

Hi Captain Snuggles,

Try the code below, which will list all three permuations of any
comma-delimited string with up to 41 elements - after which Excel runs out
of rows.

The first two subs are called by the Permutations sub and just do general
housekeeping. Progress is reported on the status bar, which probably isn't
important for a string with six elements

Option Explicit
Dim SBar As Boolean

Private Sub MacroEntry()
'Store Status Bar Visibility
SBar = Application.DisplayStatusBar
'Force Status Bar Visibility
Application.DisplayStatusBar = True
' Suspend ScreenUpdating
Application.ScreenUpdating = False
' Stop Re-Calcs
Application.Calculation = xlManual
End Sub

Private Sub MacroExit()
' Restore Re-Calcs
Application.Calculation = xlAutomatic
' Remove Message From Status Bar
Application.StatusBar = False
' Restore Status Bar Visibility
Application.DisplayStatusBar = SBar
' Restore ScreenUpdating
Application.ScreenUpdating = True
End Sub

Sub Permutations()
Dim MyArray
Dim i As Integer, j As Integer, k As Integer
Dim l As String, m As String, n As String
Call MacroEntry
MyArray = Split(ActiveCell, ",")
For i = 0 To UBound(MyArray)
l = MyArray(i)
For j = 0 To UBound(MyArray)
If j < i Then
m = l & "," & MyArray(j)
For k = 0 To UBound(MyArray)
If k < i And k < j Then
n = m & "," & MyArray(k)
ThisWorkbook.ActiveSheet.Range("A65536"). _
End(xlUp).Offset(1).Value = n
Application.StatusBar = "Processing Elements " _
& i + 1 & ", " & j + 1 & " & " & k + 1
End If
Next
End If
Next
Next
Call MacroExit
End Sub

Cheers

--
macropod
[MVP - Microsoft Word]


"Captain Snuggles" wrote in message
oups.com...
Here's what I want to do. I have 6 numbers (1, 2, 3, 4, 5 and 6) and I
need to break these six numbers into sets of 3 so that each number is
grouped with 2 other unique numbers with no repeat numbers. So, you
can't pair 1,1,1 or 1,12, each number has to be unique. There should
be 120 different sets of numbers, but I can't find a macro or formula
to do this. Any ideas?
Ideally I'd like it to look like this:
1,2,3
1,2,4
1,2,5
1,2,6
1,3,4
1,3,5...and so on.

Captain Snuggles wrote:
Here's what I want to do. I have 6 numbers (1, 2, 3, 4, 5 and 6) and I
need to break these six numbers into sets of 3 so that each number is
grouped with 2 other unique numbers with no repeat numbers. So, you
can't pair 1,1,1 or 1,12, each number has to be unique. There should
be 120 different sets of numbers, but I can't find a macro or formula
to do this. Any ideas?
Ideally I'd like it to look like this:
1,2,3
1,2,4
1,2,5
1,2,6
1,3,4
1,3,5...and so on.

Another play to tinker with ..

Here's a link to a sample book with Myrna Larson's sub implemented:
http://savefile.com/files/1635536
MyrnaLarson_Combination_Permutation.xls

In Sheet1, just make the settings as:

Put in A1: P
Put in A2: 3

List the 6 items in A3:A8, viz. in this instance,
list the 6 numbers: 1,2,3,4,5,6

Then select A1, and click the button "ListPermutations"

The 120 permutations of 3 from 6 [i.e.: =PERMUT(6,3)]
will be listed in a new sheet to the left
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---