Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Lucia
 
Posts: n/a
Default 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   Report Post  
Gord Dibben
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Generating Invoice Numbers Sarah Excel Worksheet Functions 1 January 28th 05 07:51 PM
Keyboard Shortcuts combinations of function keys, c... FJM Excel Worksheet Functions 1 January 18th 05 12:35 AM
Words > Numbers (i.e. Vanity Phone Numbers) function Don Excel Worksheet Functions 1 December 29th 04 07:10 PM
generating Unique ID numbers for students Maltenrazer Excel Worksheet Functions 2 November 6th 04 01:32 AM
generating Unique ID numbers for students Maltenrazer Excel Worksheet Functions 0 November 6th 04 12:59 AM


All times are GMT +1. The time now is 08:04 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"