If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#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... |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
What if the back-end "moves"? | John S. Ford, MD | General Discussion | 13 | November 15th, 2004 09:33 PM |
How do I identify different numbers to a table of numbers | Erich Niemand | Worksheet Functions | 1 | September 9th, 2004 04:25 AM |
COUNTIF function for a range of numbers | MugsyMoo | Worksheet Functions | 2 | February 21st, 2004 06:22 PM |