Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Function generating all possible combinations of set of numbers
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... |
#2
|
|||
|
|||
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 InsertModule. Paste the code in there. Save the workbook and hit ALT + Q to return to your workbook. ToolsMacroMacros. Select the macro "getstring" and run it. Gord Dibben Excel MVP On Mon, 7 Feb 2005 13:47:05 -0800, "Lucia" 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... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Generating Invoice Numbers | Excel Worksheet Functions | |||
Keyboard Shortcuts combinations of function keys, c... | Excel Worksheet Functions | |||
Words > Numbers (i.e. Vanity Phone Numbers) function | Excel Worksheet Functions | |||
generating Unique ID numbers for students | Excel Worksheet Functions | |||
generating Unique ID numbers for students | Excel Worksheet Functions |