Function for combinations

[Robert Crandal]

Suppose I have all possible combinations of a set of 7
numbers chosen 5 at a time. (i.e 7-choose-5). There would
be a set of 21 total combos, which are listed below:

Combo #1) 1-2-3-4-5
Combo #2) 1-2-3-4-6
Combo #3) 1-2-3-4-7
Combo #4) 1-2-3-5-6
Combo #5) 1-2-3-5-7
Combo #6) 1-2-3-6-7
Combo #7) 1-2-4-5-6
Combo #8) 1-2-4-5-7
Combo #9) 1-2-4-6-7
Combo #10 1-2-5-6-7
Combo #11) 1-3-4-5-6
Combo #12) 1-3-4-5-7
Combo #13) 1-3-4-6-7
Combo #14) 1-3-5-6-7
Combo #15) 1-4-5-6-7
Combo #16) 2-3-4-5-6
Combo #17) 2-3-4-5-7
Combo #18) 2-3-4-6-7
Combo #19) 2-3-5-6-7
Combo #20) 2-4-5-6-7
Combo #21) 3-4-5-6-7

I'm trying to devise some sort of function that will associate a
unique identifier for each combination. So, if the combination of
"1 2 3 4 5" is given as input, then my function should return a "1"
(since this is the 1st combo in the list). If "1 3 4 5 7" is given as
input, then the number 12 should be returned, since that is the 12th combo
above. Does that make sense?

I'm basically trying to find a way to generate a unique identifier for any
5-combination that is given as input. It would be nice if the solution
could
be applied to a general set of combos, such as N-choose-5.

Does anyone have any ideas? I know this problem isn't really related to
VBA, but I trust the wisdom of everybody here.

Thanks everyone.

 

[Vacuum Sealed]

Hi Rob

I'm not that flash with Excel Combo boxes ( Access, not a problem ),
that said!

If you know how to tie the combobox to this then it maybe a winner for you.

Sub myCombo()

Dim ComboSelect As Range
Dim ComboTag As Range

Set ComboSelect = Range("A2:A23") 'Combobox range
Set ComboTag = [B2] 'Corresponding Combo # output cell

Select Case ComboSelect

Case Is = "1-2-3-4-5"
ComboTag.Value = 1
Case Is = "1-2-3-4-6"
ComboTag.Value = 2
Case Is = "1-2-3-4-7"
ComboTag.Value = 3
Case Is = "1-2-3-5-6"
ComboTag.Value = 4
Case Is = "1-2-3-5-7"
ComboTag.Value = 5
Case Is = "1-2-3-6-7"
ComboTag.Value = 6
Case Is = "1-2-4-5-6"
ComboTag.Value = 7
Case Is = "1-2-4-5-7"
ComboTag.Value = 8
Case Is = "1-2-4-6-7"
ComboTag.Value = 9
Case Is = "1-2-5-6-7"
ComboTag.Value = 10
Case Is = "1-3-4-5-6"
ComboTag.Value = 11
Case Is = "1-3-4-5-7"
ComboTag.Value = 12
Case Is = "1-3-4-6-7"
ComboTag.Value = 13
Case Is = "1-2-5-6-7"
ComboTag.Value = 14
Case Is = "1-4-5-6-7"
ComboTag.Value = 15
Case Is = "2-3-4-5-6"
ComboTag.Value = 16
Case Is = "2-3-4-5-7"
ComboTag.Value = 17
Case Is = "2-3-4-6-7"
ComboTag.Value = 18
Case Is = "2-3-5-6-7"
ComboTag.Value = 19
Case Is = "2-4-5-6-7"
ComboTag.Value = 20
Case Is = "3-4-5-6-7"
ComboTag.Value = 21

End Select

End Sub

Of course If I have it wrong, one of the many talented Guru's will
correct it.

HTH
Mick.

 

[GS]

Robert Crandal brought next idea :

Suppose I have all possible combinations of a set of 7
numbers chosen 5 at a time. (i.e 7-choose-5). There would
be a set of 21 total combos, which are listed below:

Combo #1) 1-2-3-4-5
Combo #2) 1-2-3-4-6
Combo #3) 1-2-3-4-7
Combo #4) 1-2-3-5-6
Combo #5) 1-2-3-5-7
Combo #6) 1-2-3-6-7
Combo #7) 1-2-4-5-6
Combo #8) 1-2-4-5-7
Combo #9) 1-2-4-6-7
Combo #10 1-2-5-6-7
Combo #11) 1-3-4-5-6
Combo #12) 1-3-4-5-7
Combo #13) 1-3-4-6-7
Combo #14) 1-3-5-6-7
Combo #15) 1-4-5-6-7
Combo #16) 2-3-4-5-6
Combo #17) 2-3-4-5-7
Combo #18) 2-3-4-6-7
Combo #19) 2-3-5-6-7
Combo #20) 2-4-5-6-7
Combo #21) 3-4-5-6-7

I'm trying to devise some sort of function that will associate a
unique identifier for each combination. So, if the combination of
"1 2 3 4 5" is given as input, then my function should return a "1"
(since this is the 1st combo in the list). If "1 3 4 5 7" is given as
input, then the number 12 should be returned, since that is the 12th combo
above. Does that make sense?

I'm basically trying to find a way to generate a unique identifier for any
5-combination that is given as input. It would be nice if the solution could
be applied to a general set of combos, such as N-choose-5.

Does anyone have any ideas? I know this problem isn't really related to
VBA, but I trust the wisdom of everybody here.

Thanks everyone.

Hi Robert,
This smacks of Lottery Wheeling. My advice is to use an array for the
combinations and use its index for the identifier. I have a VBA lottery
wheel generator that assigns 'ticket#' as an identifier based on array
index of the wheel combination, as follows...

vTicketID(i, 0) = "Ticket" & CStr(i + 1)

...being that the array base is zero. This is the first element
(vTicketID(0, 0), for example, of an abreviated wheel that generates 42
(5, 6 or 7 num) combinations with 5/6 odds. In a 7 num draw the UBound
of the 2nd dim of the array is 7, giving an eight element array for the
1st ticket. UBound(vTicketID) is 41.

So.., in your scenario you'd use a 6 element array for each combination
as follows...

For i = LBound(vCombos) To NumTicketsWheeled - 1
vCombos(i, 0) = "Combo #) & CStr(i + 1)
vCombos(i, 1) = vWheel(0)
vCombos(i, 2) = vWheel(1)
vCombos(i, 3) = vWheel(2)
vCombos(i, 4) = vWheel(3)
vCombos(i, 5) = vWheel(4)
Next 'i

Alternatively, you could process each combination separately and write
directly to the worksheet...

Cells(i + 1, 1) = "Combo #" & CStr(i + 1)
Cells(i + 1, 2).Resize(1, 5) = vWheel

HTH

 

[Robert Crandal]

combinations and use its index for the identifier.

For now, suppose that you do NOT have the full list of combos stored in
an array. Suppose I gave you the following 5-combination as input:

"15 23 26 29 40"

Can you think of a good algorithm or scheme for computing the index
number of this combination??? I'm looking for ways to assign unique
identifiers for ANY general combination that might involve N numbers
chosen K at a time. Does that make sense??

Thanks for your help again everyone.

 

[isabelle]

hi Robert,

this is an example to get started


Sub combin()
Dim nbchiff As Integer, nb As Integer, plage As Range, cel As Range, list
list = Array(15, 23, 26, 29, 40)
nbchiff = InputBox("nombre de chiffre ?")
For nb = 1 To nbchiff
Cells(nb, 1) = (0 + list(nb - 1))
Next
Cells(nbchiff, 1).Select
bi:
Set plage = Range(Cells(1, ActiveCell.Column), ActiveCell)
plage.Cells(1).Offset(0, 1).Select
For Each cel In plage
For nb = 1 To nbchiff
ActiveCell = cel & (",") & (1 + list(nb - 1))
ActiveCell.Offset(1, 0).Select
Next nb
Next cel
ActiveCell.Offset(-1, 0).Select
If ActiveCell.Column < nbchiff Then GoTo bi
End Sub



--
isabelle



Le 2012-01-19 22:11, Robert Crandal a écrit :

 

[isabelle]

you can increase performance by removing ".Select"
it is a macro "Excel4" that i found in my archives and i translated it as such in vba

--
isabelle



Le 2012-01-19 23:54, isabelle a écrit :

 

[GS]

For now, suppose that you do NOT have the full list of combos stored in
an array. Suppose I gave you the following 5-combination as input:

"15 23 26 29 40"

Can you think of a good algorithm or scheme for computing the index
number of this combination??? I'm looking for ways to assign unique
identifiers for ANY general combination that might involve N numbers
chosen K at a time. Does that make sense??

Thanks for your help again everyone.

Using an array index will always give you a unique ID. You can also use
a Collection object's 'Key', or the Scripting.Dictionary's 'Key' since
both of those will always be unique because they will raises an error
if you try to add dupes. (**Note** that VB's/VBA's Collection will
process considerably faster than using Scripting's Dictionary) In any
case, you need to increment a counter to create unique keys and so this
brings us back to using an array. Possibly, you'll want to use a 1
based array rather than zero based. I use zero based because my
wheeling combos are stored in ranges where the first cell is the wheel
config, which always occupies array(0) so ticket numbering starts at
array(1) on up to the UBound. So an array with UBound of 42 contains 43
elements; 0 + 42 tickets.

The wheeling algorithms I use are the most popular used for 5+ odds,
and are publicly available online. Just google "lotto wheels". There
are many other configurations for various odds, though.

How they work is for a given set of numbers (say 12, for example) how
many 5, 6, or 7 number combinations can be created to return the odds
using only the 12 numbers in the set. Sets to be wheeled can contain
any number of numbers (I only use 16 max), and resulting combinations
can contain any number of the numbers in the set. For example, you can
generate 5 number combos using 20 number sets. The more numbers in a
set the more combos can be generated. Depending on this criteria, the
number of combos could be in the thousands if you want every possible
combination. To get an idea of the magnitude, check out the odds of a
49 number lottery that draws 6 or even 7 numbers per winning ticket.

I don't actually play the lottery, it's just fun to run wheels and
check them against draw histories.

 

[Robert Crandal]

Excuse my ignorance, but what is that "nombre de chiffre"
in the InputBox below??

 

[Robert Crandal]

Using an array index will always give you a unique ID. You can also use a
Collection object's 'Key', or the Scripting.Dictionary's 'Key' since both
of those will always be unique because they will raises an error if you
try to add dupes. (**Note** that VB's/VBA's Collection will process
considerably faster than using Scripting's Dictionary) In any case, you
need to increment a counter to create unique keys and so this brings us
back to using an array. Possibly, you'll want to use a 1 based array
rather than zero based. I use zero based because my wheeling combos are
stored in ranges where the first cell is the wheel config, which always
occupies array(0) so ticket numbering starts at array(1) on up to the
UBound. So an array with UBound of 42 contains 43 elements; 0 + 42
tickets.

Hmmmm, I'm looking for a solution that still doesn't involve the use
of array indexes.... I'll try to rephrase my question again, but with a
different scenario. (My bad for not being clear in the first place)

Suppose that my lottery contains the numbers 1 through 39, and only
5 numbers are drawn, which means that this lottery contains 575,757
unique combinations. My program will NEVER attempt to generate
all possible 575,757 combos, therefore, using an array index is not an
option

All my program does at the moment is ask the user to input any 5-combo
they choose, in the form of a string or whatever. If the user enters a
string
of "1 2 3 5 10", how can you calculate that this combo might occur at
position
#24 in the combo list (without using an array)? How might you be able to
calculate that combo "1 2 3 4 5" occurs at position #1?? It would be ideal
if I could calculate either the "postion number" of any combo, or simply
calculate any type of unique identifier for each combo.

Does that make more sense?

Somebody pointed me in the direction of the following website for a
solution: http://en.wikipedia.org/wiki/Combinatorial_number_system
The problem is, I don't really understand much of the jargon or notations
on that webpage, but it seems to be related to the problem I have.

Sorry if I confused u again, Mr. GS.

 

[isabelle]

oops sorry,

replace

nbchiff = InputBox("nombre de chiffre ?")

by

nbchiff = 5

--
isabelle



Le 2012-01-20 05:11, Robert Crandal a écrit :

 

[GS]

Robert,
I just took a look at the code behind my "AddTicket" procedure to see
exactly how I handled ticket numbering. Turns out my code inserts a
formula as follows...

=TicketNumber

...which took me to the Define Name dialog to see how this was
constructed. Turns out this uses row position relative to the header
row above where the first draw record starts. Draw win nums are listed
on the left side of the wks. Ticket nums/combos are listed to the right
of the draw history cols. Analysis/Wheeling section is to the right of
tickets list.

The RefersTo for the defined name formula "TicketNumber" is...

="Ticket #"&(ROW()-ROW('649'!TicketsToCheck_Hdr))

...where "TicketsToCheck_Hdr is located in Rows(6), and so tickets start
listing in Rows(7). So the above formula simply subtracts 6 from
whatever row the formula is in and concatenates the result to the text
shown. I'm sure I did this so I could re-order ticket combos if
generating 'Quick Picks' instead (or along with) wheeled combos using a
preferred list of nums akin to 'hot' or 'fav#' as determined by
criteria in the analysis area.

This RefersTo refs the locally scoped defined name range on
Sheets("649"). Sheets("749") also contains a range with the same
locally scoped defined name, as would any other sheet for any other
draw. Since our national scope draws are only 2 in count (649 <6/49> &
LottoMax <7/49>), these are the only lottery games I'm tracking.
There's no reason, though, why a Sheets("539") can't be added to the
wkb. I haven't included any 5/39 or 6/39 wheels but I know tonnes of
them exist out there in cyberspace. Each sheet contains a DV dropdown
in the Wheeling section so I can choose which wheel config to use for
generating ticket combos. The list only contains those wheel configs
that apply to the lottery game for that sheet. The wheel algorithms are
stored on a hidden sheet in horizontal ranges, and are grouped
according to the game they apply to (ie: 649, 749). For example, wheel
config '12Numbers 42Tickets 5/6Odds' will be used to generate 42
tickets using 12 specified numers that could yield 5/6 odds if all nums
drawn are in the 12 nums specified. The specified list can be
prioritized for concentration purposes since these wheel algorithms are
'abbreviated', meaning they don't generate every possible num combo.
--

I'm not sure I follow when you state...

"If the user enters a string of "1 2 3 5 10", how can you calculate
that this combo might occur at position #24 in the combo list (without
using an array)? How might you be able to calculate that combo "1 2 3
4 5" occurs at position #1??"

...which suggests that each combo may have some criteria that determines
where it 'belongs' in the list. 'Next ticket#' is my approach.

Does this help?

 

[Robert Crandal]

I don't actually play the lottery, it's just fun to run wheels and
check them against draw histories.

It's okay to play the lottery. What if your wheel hits, but you never
bet any money on it?? You're gonna feel bad forever. Which games
do you usually wheel?? 6 number games with 42 numbers???
just curious

 

[John Coleman]

Hmmmm, I'm looking for a solution that still doesn't involve the use
of array indexes....  I'll try to rephrase my question again, but with a
different scenario.   (My bad for not being clear in the first place)

Suppose that my lottery contains the numbers 1 through 39, and only
5 numbers are drawn, which means that this lottery contains 575,757
unique combinations.   My program will NEVER attempt to generate
all possible 575,757 combos, therefore, using an array index is not an
option

All my program does at the moment is ask the user to input any 5-combo
they choose, in the form of a string or whatever.   If the user enters a
string
of "1 2 3 5 10", how can you calculate that this combo might occur at
position
#24 in the combo list (without using an array)?   How might you be ableto
calculate that combo "1 2 3 4 5" occurs at position #1??  It would be ideal
if I could calculate either the "postion number" of any combo, or simply
calculate any type of unique identifier for each combo.

Does that make more sense?

Somebody pointed me in the direction of the following website for a
solution:  http://en.wikipedia.org/wiki/Combinatorial_number_system
The problem is, I don't really understand much of the jargon or notations
on that webpage, but it seems to be related to the problem I have.

Sorry if I confused u again, Mr. GS.

Greetings:

I wrote the following function from that Wikipedia page. I tested it
on all 10 combinations of 3 numbers drawn from {1,2,3,4,5} and it
seems to work. The order it returns is not the same order that you
asked for in your original post but it succeeds in returning a unique
number in the range 1 to (n choose k) for all combinations of k
elements given as space-delimited strings where the numbers are listed
in increasing order:

Function CRank(combo As String) As Long
Dim r As Long, i As Long, j As Long, k As Long
Dim nums As Variant

nums = Split(combo)
k = UBound(nums)
r = 1
For i = 1 To k + 1
j = Int(nums(i - 1))
If j > i Then
r = r + Application.WorksheetFunction.Combin(j - 1, i)
End If
Next i
CRank = r
End Function

Hope that helps

 

[John Coleman]

Greetings:

I wrote the following function from that Wikipedia page. I tested it
on all 10 combinations of 3 numbers drawn from {1,2,3,4,5} and it
seems to work. The order it returns is not the same order that you
asked for in your original post but it succeeds in returning a unique
number in the range 1 to (n choose k) for all combinations of k
elements given as space-delimited strings where the numbers are listed
in increasing order:

Function CRank(combo As String) As Long
    Dim r As Long, i As Long, j As Long, k As Long
    Dim nums As Variant

    nums = Split(combo)
    k = UBound(nums)
    r = 1
    For i = 1 To k + 1
        j = Int(nums(i - 1))
        If j > i Then
            r = r + Application.WorksheetFunction.Combin(j - 1, i)
        End If
    Next i
    CRank = r
End Function

Hope that helps- Hide quoted text -

- Show quoted text -

Here is another approach that returns the ordering from your original
post:


Function Choose(n As Long, k As Long) As Long
If n < k Then
Choose = 0
Else
Choose = Application.WorksheetFunction.Combin(n, k)
End If
End Function

Function ComboRank(combo As String, n As Long) As Long
Dim i As Long, k As Long
Dim nums As Variant
Dim sum As Long

nums = Split(combo)
k = UBound(nums)
For i = 0 To k
sum = sum + Choose(n - Int(nums(i)), k + 1 - i)
Next i
ComboRank = Choose(n, k + 1) - sum
End Function


With this approach you need to specify the number of elements in the
set being chosen from (7 in your original example)

For example, ComboRank("1 3 4 5 6",7) returns 11, which corresponds to
1-3-4-5-6 being the 11th entry in your original list

 

[GS]

Robert Crandal submitted this idea :

It's okay to play the lottery. What if your wheel hits, but you never
bet any money on it?? You're gonna feel bad forever. Which games
do you usually wheel?? 6 number games with 42 numbers???
just curious

Well, I'm living on a pension and so I have to budget my spending.
Adding to the $$ burden is the reality of living with ALS (Lou
Gehrig's), which has unbelievable financial burdens attached. I just
monitor/track our (Canada's) 2 national lotteries to see how the
wheeling algorithms fair after each draw. The '6/49' (Lotto649) happens
Wed/Sat and the 7/49 (LottoMax) happens on Fri.

Did you check John Coleman's offering for generating numbers from a
chosen list? Might be good way to do 'selective' Quick Picks as an
alternative to wheeling. My app's Quick Picks select from all 49 nums
same as the lotto terminals do. John's function might be worth adding
(with some modification) to my app as an 'extra' feature in the
wheeling section.

 

[GS]

GS was thinking very hard :

Robert Crandal submitted this idea :

Well, I'm living on a pension and so I have to budget my spending. Adding to
the $$ burden is the reality of living with ALS (Lou Gehrig's), which has
unbelievable financial burdens attached. I just monitor/track our (Canada's)
2 national lotteries to see how the wheeling algorithms fair after each draw.
The '6/49' (Lotto649) happens Wed/Sat and the 7/49 (LottoMax) happens on Fri.

Did you check John Coleman's offering for generating numbers from a chosen
list? Might be good way to do 'selective' Quick Picks as an alternative to
wheeling. My app's Quick Picks select from all 49 nums same as the lotto
terminals do. John's function might be worth adding (with some modification)
to my app as an 'extra' feature in the wheeling section.

Well.., I see that this nor John's 2nd offering addresses the
'selective' Quick Pick issue. Not sure, though, if it's how you want to
index your combos...