# Function generating all possible combinations of set of numbers

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. ## Re: Function generating all possible combinations of set of numbers

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

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)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1