Generate List of 5-digit Numbers from 7 numbers 1

[jdegeorge]

Hi

I have 7 numbers and I want to get all the combinations without any of the digits repeating. I know from Excel's COMBIN(7,5) function that there are 21 possible combinations.

Without having to sit there and manually write them out, is there a way to generate the list?

I'd appreciate any help.

Thanks!

Jim DeGeorge

 

[GlennUK]

Not easily.

This routine generates all combinations of 5 from 7, using recursion ( inherited ... can't get to my own machine today ):

'--------------------------------------------------------------------------------
Dim i As Long

Sub Combinations()
Dim n As Integer, m As Integer
i = 0
n = 7
If n = 0 Then Exit Sub
m = 5
Application.ScreenUpdating = False
Comb2 n, m, 1, ""

End Sub

'Generate combinations of integers k..n taken m at a time, recursively
Private Function Comb2(ByVal n As Integer, ByVal m As Integer, _
ByVal k As Integer, ByVal s As String)
If m > n - k + 1 Then Exit Function
If m = 0 Then
i = i + 1
Cells(i, 1) = s
Exit Function
End If
Comb2 n, m - 1, k + 1, s & k & " "
Comb2 n, m, k + 1, s
End Function

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jdegeorge]

Glenn...Is this Excel code or Access?

Jim DeGeorge

 

[GlennUK]

You mentioned Excel's COMBIN function, so I assumed you were requesting an Excel answer. If Excel is OK for you, paste the code into a VBA module ( press Alt-F11, select your book in the Project window, do menu command insert module ). Running the routine Combinations ( from the Excel menu, do Tools/Macro etc etc ), will fill cells A1:A21 with strings containing 5 numbers.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jdegeorge]

Very nice! How do I modify that to replace 1 through 7 with my numbers? I didn't see anything like an array or list of those numbers to edit.

Jim DeGeorge

 

[GlennUK]

You are better off messing around with the results as they stand, using those numbers as indices into a list of your numbers. To start with I'd do a menu command Data/Text To Columns to get each number in a different cell, and then use something like =INDEX(MyListOfNumbers,A1) to get each number of the combination.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[jdegeorge]

Glenn

I ran the macro then using a series of FIND/REPLACEs changed the values to what I needed. Thanks, and enjoy the star!

Jim DeGeorge