Myrna Larson and other codes combinations

[olives]

I'm impressed with stuff here for listing combinations. I may not understand the code, but it's impressive. I've read
some threads that mention the Myrna Larson code for Combinations, but I have no idea how it works, how to implement it or if it's the best way to do combinations for my issue. The threads I read were pretty old.

My problem:

To start, I want to list the possible 9-number combinations of the following set of numbers:
1, 2, 3, 4 , 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19 and 20.

So, for example, the results would read something like this:


1 2 3 4 5 6 7 8 9
1 2 3 4 5 6 7 8 10
1 2 3 4 5 6 7 8 11
1 2 3 4 5 6 7 8 12
1 2 3 4 5 6 7 8 13...
up to 12, 13, 14, 15, 16, 17, 18, 19, 20

I know there are modules out there that do this, but just wanted to see if someone would point out to me the most user friendly ones. I use Excel 2007 at home, but have 2003 at the office.

I got a pretty good answer from user shg at Excel forums. He pointed me to the file at:

https://www.box.com/s/b9b9fc06beb63b9562f9


The deal with the results in that spreadsheet are:

1. All the combinations are 'dumped' into one cell. I'd like the groups to have the individual members of that group to each be assigned to an their own individual cell.

2. In that spreadsheet, they're presented in the inverse order I would want them.

Instead of, in the case of 4-number groups :
1 2 3 4
1 2 3 5
1 2 3 6
1 2 3 7...

You get:

4 3 2 1

5 3 2 1

5 4 2 1

5 4 3 1

<tbody>
</tbody>

What I do like about that spreadsheet is that since eventually I'd like to form 5-number, 6-number, 7-number and 8-number goups, I can create them because the spreadsheet lets me choose the amount of items in the group.

Anybody have any spreadsheets, code or macros they recommend? Please let me know. By the way, I'm new to this, so please spare no description to what I have to do (Copy the code to a new module, download a spreadsheet...) Thanks!

 

[diddi]

olives, there will be 9C20 combinations to list... that is 167960 just for the 9 number combinations. what do you want to do with all this data?

 

[olives]

... what do you want to do with all this data?

diddi, I found another way to do it. Someone in another thread suggested this code:

Sub Combinations()
Dim n As Integer, m As Integer, numcomb
numcomb = 0
n = InputBox("Number of items?", "Combinations")
m = InputBox("Taken how many at a time?", "Combinations")
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
ActiveCell = s
ActiveCell.Offset(1, 0).Select
Exit Function
End If
Comb2 n, m - 1, k + 1, s & k & " "
Comb2 n, m, k + 1, s
End Function

And when I get the results, though I get all the items of a group in one cell, I can just change them from text to columns and then transpose the data (Thanks to sgh from Excel Forums and Special-K here!)

What I wanted to do was be able to assign values to these combinations based on market value of the individual items, so say I had a limited budget and a profit margin for each item, I could decide which 9 items from a possible 20 items I can sell might yield my highest return... make sense? Thanks anyways...