Is there a worksheet function that will generate all possible combinations of
a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
318, 381, 813, 831 and so on...
Is there a worksheet function that will generate all possible combinations of
a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
318, 381, 813, 831 and so on...
Lucia
The COMBIN and PERMUT Functions will tell you how many of each there would be,
but not print out the combinations.
To have cells filled with the actual combinations copy/paste this code to a
General Module. Good up to 8 numbers.
To see the original code and/or download a workbook, see John Walkenbach's
site.......
http://www.j-walk.com/ss/excel/tips/tip46.htm
Dim CurrentRow
Sub GetString()
Dim InString As String
msg = "Do You Want to Add a Sheet Y/N" & Chr(13) _
& "If No, Column A Will be Overwritten"
Ans = MsgBox(msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
Sheets.Add
Case vbNo
GoTo carryon
Case vbCancel
Cancel = True
Exit Sub
End Select
carryon:
InString = InputBox("Enter text to permute:")
If Len(InString) < 2 Then Exit Sub
If Len(s) >= 8 Then
MsgBox "Too many permutations!"
Exit Sub
Else
ActiveSheet.Columns(1).Clear
CurrentRow = 1
Call GetPermutation("", InString)
End If
End Sub
Sub GetPermutation(x As String, y As String)
' The source of this algorithm is unknown
Dim i As Integer, j As Integer
j = Len(y)
If j < 2 Then
Cells(CurrentRow, 1) = x & y
CurrentRow = CurrentRow + 1
Else
For i = 1 To j
Call GetPermutation(x + Mid(y, i, 1), _
Left(y, i - 1) + Right(y, j - i))
Next
End If
End Sub
If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
First...create a backup copy of your original workbook.
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Tools>Macro>Macros. Select the macro "getstring" and run it.
Gord Dibben Excel MVP
On Mon, 7 Feb 2005 13:47:05 -0800, "Lucia" <Lucia@discussions.microsoft.com>
wrote:
>Is there a worksheet function that will generate all possible combinations of
>a set of given numjbers. For example, 1, 3 and 8 would generate 138, 183,
>318, 381, 813, 831 and so on...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks