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...

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...