Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all number combination from a list
Sub Combinations()
Dim n As Integer, m As Integer Dim v As Variant, rng As Range numcomb = 0 ' Numbers should start in A1 and be in the first row Set rng = Range("A1:T1") '<== change to reflect the location of your numbers 'Set rng = rng.Resize(1, 5) v = Application.Transpose(Application _ .Transpose(rng)) n = UBound(v, 1) 'm = InputBox("Taken how many at a time?", "Combinations") m = 6 If Application.Combin(n, m) 64530 Then MsgBox "Too many to write out, quitting" Exit Sub End If Range("A3").Select Comb2 n, m, 1, "'", v End Sub 'Generate combinations of integers k..n taken m at a time, recursively Sub Comb2(ByVal n As Integer, ByVal m As Integer, _ ByVal k As Integer, ByVal s As String, v As Variant) Dim v1 As Variant If m n - k + 1 Then Exit Sub If m = 0 Then 'Debug.Print "-" & s & "<-" v1 = Split(Replace(Trim(s), "'", ""), " ") For i = LBound(v1) To UBound(v1) ActiveCell.Offset(0, i) = v(v1(i)) Next ActiveCell.Offset(1, 0).Select Exit Sub End If Comb2 n, m - 1, k + 1, s & k & " ", v Comb2 n, m, k + 1, s, v End Sub -- Regards, Tom Ogilvy " wrote: Dear all, I am new in excel programming. I need to get all the 6-number combination from a list, how? To illustration, The list is, for example, 1,2,3,4,5,6,7,8,9,...10 The 6-number combination are combintation 1) 1,2,3,4,5,6 combintation 2) 1,2,3,4,5,7 ..... How to get all combination? Thanks for your inputs in advance, Wing |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to get all number combination from a list
On 4ÔÂ11ÈÕ, ÏÂÎç11ʱ38·Ö, Tom Ogilvy
wrote: Sub Combinations() Dim n As Integer, m As Integer Dim v As Variant, rng As Range numcomb = 0 ' Numbers should start in A1 and be in the first row Set rng = Range("A1:T1") '<== change to reflect the location of your numbers 'Set rng = rng.Resize(1, 5) v = Application.Transpose(Application _ .Transpose(rng)) n = UBound(v, 1) 'm = InputBox("Taken how many at a time?", "Combinations") m = 6 If Application.Combin(n, m) 64530 Then MsgBox "Too many to write out, quitting" Exit Sub End If Range("A3").Select Comb2 n, m, 1, "'", v End Sub 'Generate combinations of integers k..n taken m at a time, recursively Sub Comb2(ByVal n As Integer, ByVal m As Integer, _ ByVal k As Integer, ByVal s As String, v As Variant) Dim v1 As Variant If m n - k + 1 Then Exit Sub If m = 0 Then 'Debug.Print "-" & s & "<-" v1 = Split(Replace(Trim(s), "'", ""), " ") For i = LBound(v1) To UBound(v1) ActiveCell.Offset(0, i) = v(v1(i)) Next ActiveCell.Offset(1, 0).Select Exit Sub End If Comb2 n, m - 1, k + 1, s & k & " ", v Comb2 n, m, k + 1, s, v End Sub -- Regards, Tom Ogilvy " wrote: Dear all, I am new in excel programming. I need to get all the 6-number combinationfrom a list, how? To illustration, The list is, for example, 1,2,3,4,5,6,7,8,9,...10 The 6-numbercombinationare combintation 1) 1,2,3,4,5,6 combintation 2) 1,2,3,4,5,7 ..... How to get allcombination? Thanks for your inputs in advance, Wing- Òþ²Ø±»ÒýÓÃÎÄ×Ö - - ÏÔʾÒýÓõÄÎÄ×Ö - Hi Tom, I have modified your coding a little bit before it can be run. But after running, it produce no result even I have input the numbers in 1st row. Could you please help to check if there is anything wrong. Sub Combinations() Dim n As Integer, m As Integer Dim v As Variant, rng As Range numcomb = 0 ' Numbers should start in A1 and be in the first row Set rng = Range("A1:M1") '<== change to reflect the location of your numbers Set rng = rng.Resize(1, 13) v = Application.Transpose(Application.Transpose(rng)) n = UBound(v, 1) 'm = InputBox("Taken how many at a time?", "Combinations") m = 6 'If Application.Combin(n, m) 64530 Then MsgBox "Too many to write out, quitting" ' Exit Sub 'End If 'Range("A1:M1").Select Comb2 n, m, 1, "'", v End Sub 'Generate combinations of integers k..n taken m at a time, recursively Sub Comb2(ByVal n As Integer, ByVal m As Integer, _ ByVal k As Integer, ByVal s As String, v As Variant) Dim v1 As Variant If m n - k + 1 Then Exit Sub If m = 0 Then 'Debug.Print "-" & s & "<-" v1 = Split(Replace(Trim(s), "'", ""), " ") For i = LBound(v1) To UBound(v1) ActiveCell.Offset(0, i) = v(v1(i)) Next ActiveCell.Offset(1, 0).Select Exit Sub End If Comb2 n, m - 1, k + 1, s & k & " ", v Comb2 n, m, k + 1, s, v End Sub Thanks a lot, Ali |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Number generator and combination | Excel Discussion (Misc queries) | |||
How do I sort different combination of a 4 digits number | Excel Discussion (Misc queries) | |||
combination of six digit number from 0 to 49 | Excel Worksheet Functions | |||
Number Combination finder | Excel Worksheet Functions | |||
Number Combination | Excel Programming |