COMBINATIONS

[verluc]

Can someone advise me for a macro that can produce a list that contains all
possible combinations of 6 from 27
Thanks in advance

[s-o-s]

On 2002-05-20 03:14, verluc wrote:
Can someone advise me for a macro that can produce a list that contains all
possible combinations of 6 from 27
Thanks in advance

If my old stats knowledge is correct,

27
  C  =  296,010 combinations
   6

In which case you will have to write your list to a non excel sheet.

Is this what you want to do?

[ldry]

An Excel sheet has plenty of room - 16,777,216 cells

[s-o-s]

On 2002-05-20 05:40, ldry wrote:
An Excel sheet has plenty of room - 16,777,216 cells

A very good point, I was thinking of a verticle list. At 65000 rows it will jump across 1 column

Here's the code...Its not the best but it works!

Code has been deleted as it didn't work, 

It had duplication in it...now replaced...

See below.

Hope this helps


_________________






[ This Message was edited by: s-o-s on 2002-05-20 09:11 ]

[s-o-s]

I've found some duplication eg cell
C 21802 has

1 4 3 4 5 6

I'll have a bit more of a think...

[s-o-s]

Think I've got it this time...Send me an email if you find it doesn't work this time as I will be going off line...

Sub Combs()
Application.ScreenUpdating = False
rw = 1
cl = 0
For a = 1 To 27
    For b = (a + 1) To 27
        For c = (b + 1) To 27
            For d = (c + 1) To 27
                For e = (d + 1) To 27
                    For f = (e + 1) To 27

' Place in here the code to write your list
' what goes in here depends on how you want to export

                    If rw >= 65000 Then
                        Application.ScreenUpdating = True
                        rw = 1
                        cl = cl + 1
                        Application.ScreenUpdating = False
                    End If
                    cellval = a & " " & b & " " & c & " " & d & " " & e & " " & f
                    Range("A1").Offset(rw, cl).Value = cellval
                    rw = rw + 1
                    Next
                Next

            Next
        Next
    Next
Next
Application.ScreenUpdating = True
End Sub

Hope It works this time.

[verluc]

On 2002-05-20 05:53, s-o-s wrote:

A very good point, I was thinking of a verticle list. At 65000 rows it will jump across 1 column

Here's the code...Its not the best but it works!

Sub Combs()
application.ScreenUpdating = False
rw = 1
cl = 0
For a = 1 To 27
    For b = 2 To 27
        For c = 3 To 27
            For d = 4 To 27
                For e = 5 To 27
                    For f = 6 To 27
                        If f = e Then GoTo Eloop6
                        If f = d Then GoTo Eloop6
                        If f = c Then GoTo Eloop6
                        If f = b Then GoTo Eloop6
                        If f = a Then GoTo Eloop6

' Place in here the code to write your list
' what goes in here depends on how you want to export
                    If rw > 65000 Then
                        rw = 1
                        cl = cl + 1
                    End If
                    cellval = a & " " & b & " " & c & " " & d & " " & e & " " & f
                    Range("A1").Offset(rw, cl).Value = cellval
                    rw = rw + 1
Eloop6:
                    Next
                    If e = d Then GoTo Eloop5
                    If e = c Then GoTo Eloop5
                    If e = b Then GoTo Eloop5
                    If e = a Then GoTo Eloop5
Eloop5:
                Next
                If d = c Then GoTo Eloop4
                If d = b Then GoTo Eloop4
                If d = a Then GoTo Eloop4
Eloop4:
            Next
            If c = b Then GoTo Eloop3
            If c = a Then GoTo Eloop3

Eloop3:
        Next
        If b = a Then GoTo Eloop2
Eloop2:
    Next
Next
application.ScreenUpdating = True
End Sub

Hope this helps



ps forgot to add back in the updating screen options...now edited successfully.

_________________





[ This Message was edited by: s-o-s on 2002-05-20 06:00 ]

Hi,
Thanks for your macro.When I run it,then all
the numbers are in 1 column.
Is it possible that all numbers are in different columns.Example: A B C D E F
Thanks in advance/

[s-o-s]

OK, here's the code for that one...

Sub Combs2()
Application.ScreenUpdating = False
rw = 1
cl = 0
For a = 1 To 27
    For b = (a + 1) To 27
        For c = (b + 1) To 27
            For d = (c + 1) To 27
                For e = (d + 1) To 27
                    For f = (e + 1) To 27

                    If rw >= 65000 Then
                        Application.ScreenUpdating = True
                        rw = 1
                        cl = cl + 10
                        Application.ScreenUpdating = False
                    End If
                    cellval = a & " " & b & " " & c & " " & d & " " & e & " " & f
                    
                    Range("A1").Offset(rw, cl).Value = a
                    Range("A1").Offset(rw, cl + 1).Value = b
                    Range("A1").Offset(rw, cl + 2).Value = c
                    Range("A1").Offset(rw, cl + 3).Value = d
                    Range("A1").Offset(rw, cl + 4).Value = e
                    Range("A1").Offset(rw, cl + 5).Value = f
                    rw = rw + 1
                    Next
                Next

            Next
        Next
    Next
Next
Application.ScreenUpdating = True
End Sub

Hope this helps.