hello

who can convert that into vba

the goal is to find the index of the combination

Index Mapping

By creating a MAPPING function (CombinationToIndex) that assigns an
INDEX value to each combination or permutation according to its position
in the full list, we can represent individual combinations by simple
numbers.

With an inverse function (IndexToCombination) that returns the
combination for a given index value, we can then generate any subset of
all possible combinations from a given starting point like this:

Code:
StartIndex = CombinationToIndex(50, 6, "1, 7, 23, 35, 47, 49")
For CombNo = StartIndex To StartIndex + 99
Debug.Print IndexToCombination(50, 6, CombNo)
Next

thank you

I can, I can, I can
I did it in 1 function rather than two.


Const Forward As Integer = 0
Const Reverse As Integer = 1
Public MyNumbers() As Variant
Public SearchArray() As Variant
Public combinationArray() As Variant
Public ArraySize As Integer
Public LowerLimit As Double
Public UpperLimit As Double
Sub test()

Dim index As Long
Dim ComboNo As Double
MyNumbers = Array(1, 6, 7, 23, 35, 47, 49, 50)
SearchArray = Array(50, 6, 1, 7, 23, 35, 47, 49)
ArraySize = UBound(MyNumbers)
ReDim combinationArray(ArraySize)
index = 0
level = 0
StartIndex = CombinationToIndex(Forward, level, index)
LowerLimit = StartIndex
UpperLimit = StartIndex + 99#
index = 0
level = 0
StartIndex = CombinationToIndex(Reverse, level, index)
End Sub

Function CombinationToIndex(Mode, level, ByRef index As Long)

For i = 0 To ArraySize
combinationArray(level) = MyNumbers(i)
If level = ArraySize Then
If Mode = Forward Then
'check if search value was found
found = True
For j = 0 To ArraySize
If SearchArray(j) <> combinationArray(j) Then
found = False
Exit For
End If
Next j

If found = True Then
CombinationToIndex = index
Exit For
Else
CombinationToIndex = -1
End If
Else
If index >= LowerLimit Then
For k = 0 To ArraySize
If k = 0 Then
PrintString = CStr(combinationArray(k))
Else
PrintString = PrintString & "," & _
CStr(combinationArray(k))
End If
Next k
MsgBox (PrintString)
If index = UpperLimit Then
CombinationToIndex = 0
Exit For
Else
CombinationToIndex = -1
End If
Else
CombinationToIndex = -1
End If
End If
index = index + 1
Else
CombinationToIndex = CombinationToIndex(Mode, level + 1, index)
End If
If CombinationToIndex <> -1 Then
Exit For
End If
Next i


End Function

"PST" wrote:

> hello
>
> who can convert that into vba
>
> the goal is to find the index of the combination
>
> Index Mapping
>
> By creating a MAPPING function (CombinationToIndex) that assigns an
> INDEX value to each combination or permutation according to its position
> in the full list, we can represent individual combinations by simple
> numbers.
>
> With an inverse function (IndexToCombination) that returns the
> combination for a given index value, we can then generate any subset of
> all possible combinations from a given starting point like this:
>
> Code:
> StartIndex = CombinationToIndex(50, 6, "1, 7, 23, 35, 47, 49")
> For CombNo = StartIndex To StartIndex + 99
> Debug.Print IndexToCombination(50, 6, CombNo)
> Next
>
> thank you
>

Hi. Here are your first 6 outputs:

> 50,6,1,7,23,35,47,49
> 50,6,1,7,23,35,47,50
> 50,6,1,7,23,35,49,1
> 50,6,1,7,23,35,49,6
> 50,6,1,7,23,35,49,7
> 50,6,1,7,23,35,49,23

I may be wrong, but I think there is a logic error in the code.
I read the op's question as being that from a set of s=50, and Subsets of
size 6,
find the next Subset in Lexicographical order.
(Using another program, the next Subset appears correct:

NextKSubset[s, {1, 7, 23, 35, 47, 49}]
{1, 7, 23, 35, 47, 50}

But your third one is a little off.

NextKSubset[s, %]
{1, 7, 23, 35, 48, 49}

Also note that your 6th output has two "23's", which can not be part of a
Subset in this context.

I believe the Op is asking for the "Rank"

n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1

926,277

(ie it's the 926,277 item in the list)

and then wanting to "UnRank" it.

UnrankKSubset[n, 6, s]

{1, 7, 23, 35, 47, 50}

Again, I may be wrong.

--
Dana DeLouis

<snip>

Ther was no error in my code. I wasn't sure if the PST wanted 2 to the N
combinitions or PST was looking for N! (factorial) permutations. Sinced you
asked for permutations here is the modified code. It looks very similar
except I do a check to make sure the number isn't used more than once.

P.S. I was expecting somebody to respond with the question why were the
numbers appearing twice. A couple of weeks ago somebody posted a requested
for permutations, so I knew exactly how to make the changes before the
question was asked.


Const Forward As Integer = 0
Const Reverse As Integer = 1
Public MyNumbers() As Variant
Public SearchArray() As Variant
Public combinationArray() As Variant
Public ArraySize As Integer
Public LowerLimit As Double
Public UpperLimit As Double
Sub test()

Dim index As Long
Dim ComboNo As Double
MyNumbers = Array(1, 6, 7, 23, 35, 47, 49, 50)
SearchArray = Array(50, 6, 1, 7, 23, 35, 47, 49)
ArraySize = UBound(MyNumbers)
ReDim combinationArray(ArraySize)
index = 0
level = 0
StartIndex = CombinationToIndex(Forward, level, index)
LowerLimit = StartIndex
UpperLimit = StartIndex + 99#
index = 0
level = 0
StartIndex = CombinationToIndex(Reverse, level, index)
End Sub

Function CombinationToIndex(Mode, level, ByRef index As Long)

For i = 0 To ArraySize
found = False
For n = 0 To (level - 1)
If combinationArray(n) = i Then
found = True
Exit For
End If
Next n
If found = False Then
combinationArray(level) = i

If level = ArraySize Then
If Mode = Forward Then
'check if search value was found
found = True
For j = 0 To ArraySize
If SearchArray(j) <> _
MyNumbers(combinationArray(j)) Then

found = False
Exit For
End If
Next j

If found = True Then
CombinationToIndex = index
Exit For
Else
CombinationToIndex = -1
End If
Else
If index >= LowerLimit Then
For k = 0 To ArraySize
If k = 0 Then
PrintString = _
CStr(MyNumbers(combinationArray(k)))
Else
PrintString = PrintString & "," & _
CStr(MyNumbers(combinationArray(k)))
End If
Next k
MsgBox (PrintString)
If index = UpperLimit Then
CombinationToIndex = 0
Exit For
Else
CombinationToIndex = -1
End If
Else
CombinationToIndex = -1
End If
End If

index = index + 1
Else
CombinationToIndex = _
CombinationToIndex(Mode, level + 1, index)
End If
If CombinationToIndex <> -1 Then
Exit For
End If
End If
Next i

End Function


"Dana DeLouis" wrote:

> Hi. Here are your first 6 outputs:
>
> > 50,6,1,7,23,35,47,49
> > 50,6,1,7,23,35,47,50
> > 50,6,1,7,23,35,49,1
> > 50,6,1,7,23,35,49,6
> > 50,6,1,7,23,35,49,7
> > 50,6,1,7,23,35,49,23
>
> I may be wrong, but I think there is a logic error in the code.
> I read the op's question as being that from a set of s=50, and Subsets of
> size 6,
> find the next Subset in Lexicographical order.
> (Using another program, the next Subset appears correct:
>
> NextKSubset[s, {1, 7, 23, 35, 47, 49}]
> {1, 7, 23, 35, 47, 50}
>
> But your third one is a little off.
>
> NextKSubset[s, %]
> {1, 7, 23, 35, 48, 49}
>
> Also note that your 6th output has two "23's", which can not be part of a
> Subset in this context.
>
> I believe the Op is asking for the "Rank"
>
> n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
>
> 926,277
>
> (ie it's the 926,277 item in the list)
>
> and then wanting to "UnRank" it.
>
> UnrankKSubset[n, 6, s]
>
> {1, 7, 23, 35, 47, 50}
>
> Again, I may be wrong.
>
> --
> Dana DeLouis
>
> <snip>
>
>
>

Then you want the 2nd method. Or maybe you are asking for a 3rd problem.

This code counts factorially to the High Number.
Warning, Don't run this code because it takes forever to run. Make the
numbers smaller. The code is counting 8 wide numbers with each number going
from 1 to 50.

Doesn't all these macros look very similar? Only a few lines changed can
get you a completely different algorithm.


Example (10,5)
1,2,3,4,5
1,2,3,4,6
1,2,3,4,7
1,2,3,4,8
1,2,3,4,9
1,2,3,4,10
1,2,3,5,4
1,2,3,5,6
1,2,3,5,7
1,2,3,4,8
1,2,3,4,9
1,2,3,4,10

Const Forward As Integer = 0
Const Reverse As Integer = 1

Public SearchArray() As Variant
Public combinationArray() As Variant
Public ArraySize As Integer
Public LowerLimit As Double
Public UpperLimit As Double
Public HighNumber As Integer
Public Width As Integer
Sub test()

Dim index As Long
Dim ComboNo As Double

SearchArray = Array(50, 6, 1, 7, 23, 35, 47, 49)
HighNumber = 50
Width = 7 'starts at 0 so width really 8
index = 0
level = 0
ReDim combinationArray(Width)
StartIndex = CombinationToIndex(Forward, level, index)
LowerLimit = StartIndex
UpperLimit = StartIndex + 99#
index = 0
level = 0
StartIndex = CombinationToIndex(Reverse, level, index)
End Sub

Function CombinationToIndex(Mode, level, ByRef index As Long)

For i = 1 To HighNumber
found = False
For n = 0 To (level - 1)
If combinationArray(n) = i Then
found = True
Exit For
End If
Next n
If found = False Then
combinationArray(level) = i

If level = Width Then
If Mode = Forward Then
'check if search value was found
found = True
For j = 0 To Width
If SearchArray(j) <> _
combinationArray(j) Then

found = False
Exit For
End If
Next j

If found = True Then
CombinationToIndex = index
Exit For
Else
CombinationToIndex = -1
End If
Else
If index >= LowerLimit Then
For k = 0 To ArraySize
If k = 0 Then
PrintString = _
CStr(combinationArray(k))
Else
PrintString = PrintString & "," & _
CStr(combinationArray(k))
End If
Next k
MsgBox (PrintString)
If index = UpperLimit Then
CombinationToIndex = 0
Exit For
Else
CombinationToIndex = -1
End If
Else
CombinationToIndex = -1
End If
End If

index = index + 1
Else
CombinationToIndex = _
CombinationToIndex(Mode, level + 1, index)
End If
If CombinationToIndex <> -1 Then
Exit For
End If
End If
Next i


End Function


MyNumbers = Array(1,2,3,4,5,6)


"PST" wrote:

> thank you for your reply
>
> I think that you are right
> "I believe the Op is asking for the "Rank"
>
>
> I will want the way simplest to have the Rank
> ex: combin(20,6)
> (1,2,3,4,5,19) rank is 14
> (1,2,3 4,5,14) rank is 9
>
>
> how to have it and use the functions RankKSubset UnrankKSubs
> and they are not in Excel
>
> >>>>>
>
> I believe the Op is asking for the "Rank"
> >
> > n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
> >
> > 926,277
> >
> > (ie it's the 926,277 item in the list)
> >
> > and then wanting to "UnRank" it.
> >
> > UnrankKSubset[n, 6, s]
> >
> > {1, 7, 23, 35, 47, 50}
> >
> > Again, I may be wrong.
> >
> > --
> > Dana DeLouis
> >
> > <snip>Joel a écrit :
> > Ther was no error in my code. I wasn't sure if the PST wanted 2 to the N
> > combinitions or PST was looking for N! (factorial) permutations. Sinced you
> > asked for permutations here is the modified code. It looks very similar
> > except I do a check to make sure the number isn't used more than once.
> >
> > P.S. I was expecting somebody to respond with the question why were the
> > numbers appearing twice. A couple of weeks ago somebody posted a requested
> > for permutations, so I knew exactly how to make the changes before the
> > question was asked.
> >
> >
> > Const Forward As Integer = 0
> > Const Reverse As Integer = 1
> > Public MyNumbers() As Variant
> > Public SearchArray() As Variant
> > Public combinationArray() As Variant
> > Public ArraySize As Integer
> > Public LowerLimit As Double
> > Public UpperLimit As Double
> > Sub test()
> >
> > Dim index As Long
> > Dim ComboNo As Double
> > MyNumbers = Array(1, 6, 7, 23, 35, 47, 49, 50)
> > SearchArray = Array(50, 6, 1, 7, 23, 35, 47, 49)
> > ArraySize = UBound(MyNumbers)
> > ReDim combinationArray(ArraySize)
> > index = 0
> > level = 0
> > StartIndex = CombinationToIndex(Forward, level, index)
> > LowerLimit = StartIndex
> > UpperLimit = StartIndex + 99#
> > index = 0
> > level = 0
> > StartIndex = CombinationToIndex(Reverse, level, index)
> > End Sub
> >
> > Function CombinationToIndex(Mode, level, ByRef index As Long)
> >
> > For i = 0 To ArraySize
> > found = False
> > For n = 0 To (level - 1)
> > If combinationArray(n) = i Then
> > found = True
> > Exit For
> > End If
> > Next n
> > If found = False Then
> > combinationArray(level) = i
> >
> > If level = ArraySize Then
> > If Mode = Forward Then
> > 'check if search value was found
> > found = True
> > For j = 0 To ArraySize
> > If SearchArray(j) <> _
> > MyNumbers(combinationArray(j)) Then
> >
> > found = False
> > Exit For
> > End If
> > Next j
> >
> > If found = True Then
> > CombinationToIndex = index
> > Exit For
> > Else
> > CombinationToIndex = -1
> > End If
> > Else
> > If index >= LowerLimit Then
> > For k = 0 To ArraySize
> > If k = 0 Then
> > PrintString = _
> > CStr(MyNumbers(combinationArray(k)))
> > Else
> > PrintString = PrintString & "," & _
> > CStr(MyNumbers(combinationArray(k)))
> > End If
> > Next k
> > MsgBox (PrintString)
> > If index = UpperLimit Then
> > CombinationToIndex = 0
> > Exit For
> > Else
> > CombinationToIndex = -1
> > End If
> > Else
> > CombinationToIndex = -1
> > End If
> > End If
> >
> > index = index + 1
> > Else
> > CombinationToIndex = _
> > CombinationToIndex(Mode, level + 1, index)
> > End If
> > If CombinationToIndex <> -1 Then
> > Exit For
> > End If
> > End If
> > Next i
> >
> > End Function
> >
> >
> > "Dana DeLouis" wrote:
> >
> >> Hi. Here are your first 6 outputs:
> >>
> >>> 50,6,1,7,23,35,47,49
> >>> 50,6,1,7,23,35,47,50
> >>> 50,6,1,7,23,35,49,1
> >>> 50,6,1,7,23,35,49,6
> >>> 50,6,1,7,23,35,49,7
> >>> 50,6,1,7,23,35,49,23
> >> I may be wrong, but I think there is a logic error in the code.
> >> I read the op's question as being that from a set of s=50, and Subsets of
> >> size 6,
> >> find the next Subset in Lexicographical order.
> >> (Using another program, the next Subset appears correct:
> >>
> >> NextKSubset[s, {1, 7, 23, 35, 47, 49}]
> >> {1, 7, 23, 35, 47, 50}
> >>
> >> But your third one is a little off.
> >>
> >> NextKSubset[s, %]
> >> {1, 7, 23, 35, 48, 49}
> >>
> >> Also note that your 6th output has two "23's", which can not be part of a
> >> Subset in this context.
> >>
> >> I believe the Op is asking for the "Rank"
> >>
> >> n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
> >>
> >> 926,277
> >>
> >> (ie it's the 926,277 item in the list)
> >>
> >> and then wanting to "UnRank" it.
> >>
> >> UnrankKSubset[n, 6, s]
> >>
> >> {1, 7, 23, 35, 47, 50}
> >>
> >> Again, I may be wrong.
> >>
> >> --
> >> Dana DeLouis
> >>
> >> <snip>
> >>
> >>
> >>
>
>

thank you for your reply

I think that you are right
"I believe the Op is asking for the "Rank"


I will want the way simplest to have the Rank
ex: combin(20,6)
(1,2,3,4,5,19) rank is 14
(1,2,3 4,5,14) rank is 9


how to have it and use the functions RankKSubset UnrankKSubs
and they are not in Excel

>>>>>

I believe the Op is asking for the "Rank"
>
> n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
>
> 926,277
>
> (ie it's the 926,277 item in the list)
>
> and then wanting to "UnRank" it.
>
> UnrankKSubset[n, 6, s]
>
> {1, 7, 23, 35, 47, 50}
>
> Again, I may be wrong.
>
> --
> Dana DeLouis
>
> <snip>Joel a écrit :
> Ther was no error in my code. I wasn't sure if the PST wanted 2 to theN
> combinitions or PST was looking for N! (factorial) permutations. Sinced you
> asked for permutations here is the modified code. It looks very similar
> except I do a check to make sure the number isn't used more than once.
>
> P.S. I was expecting somebody to respond with the question why were the
> numbers appearing twice. A couple of weeks ago somebody posted a requested
> for permutations, so I knew exactly how to make the changes before the
> question was asked.
>
>
> Const Forward As Integer = 0
> Const Reverse As Integer = 1
> Public MyNumbers() As Variant
> Public SearchArray() As Variant
> Public combinationArray() As Variant
> Public ArraySize As Integer
> Public LowerLimit As Double
> Public UpperLimit As Double
> Sub test()
>
> Dim index As Long
> Dim ComboNo As Double
> MyNumbers = Array(1, 6, 7, 23, 35, 47, 49, 50)
> SearchArray = Array(50, 6, 1, 7, 23, 35, 47, 49)
> ArraySize = UBound(MyNumbers)
> ReDim combinationArray(ArraySize)
> index = 0
> level = 0
> StartIndex = CombinationToIndex(Forward, level, index)
> LowerLimit = StartIndex
> UpperLimit = StartIndex + 99#
> index = 0
> level = 0
> StartIndex = CombinationToIndex(Reverse, level, index)
> End Sub
>
> Function CombinationToIndex(Mode, level, ByRef index As Long)
>
> For i = 0 To ArraySize
> found = False
> For n = 0 To (level - 1)
> If combinationArray(n) = i Then
> found = True
> Exit For
> End If
> Next n
> If found = False Then
> combinationArray(level) = i
>
> If level = ArraySize Then
> If Mode = Forward Then
> 'check if search value was found
> found = True
> For j = 0 To ArraySize
> If SearchArray(j) <> _
> MyNumbers(combinationArray(j)) Then
>
> found = False
> Exit For
> End If
> Next j
>
> If found = True Then
> CombinationToIndex = index
> Exit For
> Else
> CombinationToIndex = -1
> End If
> Else
> If index >= LowerLimit Then
> For k = 0 To ArraySize
> If k = 0 Then
> PrintString = _
> CStr(MyNumbers(combinationArray(k)))
> Else
> PrintString = PrintString & "," & _
> CStr(MyNumbers(combinationArray(k)))
> End If
> Next k
> MsgBox (PrintString)
> If index = UpperLimit Then
> CombinationToIndex = 0
> Exit For
> Else
> CombinationToIndex = -1
> End If
> Else
> CombinationToIndex = -1
> End If
> End If
>
> index = index + 1
> Else
> CombinationToIndex = _
> CombinationToIndex(Mode, level + 1, index)
> End If
> If CombinationToIndex <> -1 Then
> Exit For
> End If
> End If
> Next i
>
> End Function
>
>
> "Dana DeLouis" wrote:
>
>> Hi. Here are your first 6 outputs:
>>
>>> 50,6,1,7,23,35,47,49
>>> 50,6,1,7,23,35,47,50
>>> 50,6,1,7,23,35,49,1
>>> 50,6,1,7,23,35,49,6
>>> 50,6,1,7,23,35,49,7
>>> 50,6,1,7,23,35,49,23
>> I may be wrong, but I think there is a logic error in the code.
>> I read the op's question as being that from a set of s=50, and Subsets of
>> size 6,
>> find the next Subset in Lexicographical order.
>> (Using another program, the next Subset appears correct:
>>
>> NextKSubset[s, {1, 7, 23, 35, 47, 49}]
>> {1, 7, 23, 35, 47, 50}
>>
>> But your third one is a little off.
>>
>> NextKSubset[s, %]
>> {1, 7, 23, 35, 48, 49}
>>
>> Also note that your 6th output has two "23's", which can not be part of a
>> Subset in this context.
>>
>> I believe the Op is asking for the "Rank"
>>
>> n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
>>
>> 926,277
>>
>> (ie it's the 926,277 item in the list)
>>
>> and then wanting to "UnRank" it.
>>
>> UnrankKSubset[n, 6, s]
>>
>> {1, 7, 23, 35, 47, 50}
>>
>> Again, I may be wrong.
>>
>> --
>> Dana DeLouis
>>
>> <snip>
>>
>>
>>

Hi. Here's another way. Note that in your example, we assume the size of
the subset from the given array of values. (we don't supply the '6).
We do have to give the size of the set though.

Sub TestYourData()
'//Easy Test (1)
Debug.Print RankKSubset(9, 1, 2, 3, 4, 5, 6)

'// Other easy test...
Debug.Print RankKSubset(9, 4, 5, 6, 7, 8, 9)
Debug.Print [Combin(9,6)]

Debug.Print RankKSubset(50, 1, 7, 23, 35, 47, 49)

Debug.Print RankKSubset(20, 1, 2, 3, 4, 5, 19)
Debug.Print RankKSubset(20, 1, 2, 3, 4, 5, 14)
End Sub

Function RankKSubset(Size, ParamArray v())
'//= = = = = = = = = = = = = = = = = =
'// By: Dana DeLouis
'//= = = = = = = = = = = = = = = = = =

Dim j As Long
Dim d As Double
Dim n As Double
Dim m As Double
Dim T As Double

With WorksheetFunction
m = Size
n = UBound(v) + 1
d = v(0)
T = T + .Combin(m, n) - .Combin(m - d + 1, n)
For j = 1 To UBound(v) - 1
m = m - d
n = n - 1
d = v(j) - v(j - 1)
T = T + .Combin(m, n) - .Combin(m - d + 1, n)
Next j
T = T + v(j) - v(j - 1)
End With
RankKSubset = T
End Function


To convert a number to a subset, we need to supply both the size of the set,
and the size of the subset.
So, in our example of 50 & 6, we note that if our number falls within
=COMBIN(49,5) = 1906884
then our first digit is 1.
If n is greater than this, then we add =COMBIN(48,5) = 1712304
If n falls within this new number, then the first number is 2...etc
We have to do these short loops because there is no analytical way to
directly solve the binomial sum (afaik)

As a side note, when your size gets too large, it can sometimes be more
efficient to call a NextPermutation routine
I've done this before, but I can't find it at the moment.
The reason for this is that if you had a size 1000, and a subset size of 14,
then this exceeds Excel's extended capabilities.
For example, if you had this size 14 subset out of 1,000:

{2, 39, 140, 230, 262, 341, 443, 558, 612, 661, 674, 705, 804, 839}

then it would be the
202,646,961,038,399,155,876,623,226,251
'th item in the list.

This slightly exceeds Excel's extended capabilities.
Hence, it is usually faster to work with the smaller array.

--
Dana DeLouis



"PST" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
thank you for your reply

I think that you are right
"I believe the Op is asking for the "Rank"


I will want the way simplest to have the Rank
ex: combin(20,6)
(1,2,3,4,5,19) rank is 14
(1,2,3 4,5,14) rank is 9


how to have it and use the functions RankKSubset UnrankKSubs
and they are not in Excel

>>>>>

I believe the Op is asking for the "Rank"
>
> n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
>
> 926,277
>
> (ie it's the 926,277 item in the list)
>
> and then wanting to "UnRank" it.
>
> UnrankKSubset[n, 6, s]
>
> {1, 7, 23, 35, 47, 50}
>
> Again, I may be wrong.
>
> --
> Dana DeLouis
>
> <snip>Joel a écrit :
> Ther was no error in my code. I wasn't sure if the PST wanted 2 to the N
> combinitions or PST was looking for N! (factorial) permutations. Sinced
> you asked for permutations here is the modified code. It looks very
> similar except I do a check to make sure the number isn't used more than
> once.
>
> P.S. I was expecting somebody to respond with the question why were the
> numbers appearing twice. A couple of weeks ago somebody posted a
> requested for permutations, so I knew exactly how to make the changes
> before the question was asked.
>
>
> Const Forward As Integer = 0
> Const Reverse As Integer = 1
> Public MyNumbers() As Variant
> Public SearchArray() As Variant
> Public combinationArray() As Variant
> Public ArraySize As Integer
> Public LowerLimit As Double
> Public UpperLimit As Double
> Sub test()
>
> Dim index As Long
> Dim ComboNo As Double
> MyNumbers = Array(1, 6, 7, 23, 35, 47, 49, 50)
> SearchArray = Array(50, 6, 1, 7, 23, 35, 47, 49)
> ArraySize = UBound(MyNumbers)
> ReDim combinationArray(ArraySize)
> index = 0
> level = 0
> StartIndex = CombinationToIndex(Forward, level, index)
> LowerLimit = StartIndex
> UpperLimit = StartIndex + 99#
> index = 0
> level = 0
> StartIndex = CombinationToIndex(Reverse, level, index)
> End Sub
>
> Function CombinationToIndex(Mode, level, ByRef index As Long)
>
> For i = 0 To ArraySize
> found = False
> For n = 0 To (level - 1)
> If combinationArray(n) = i Then
> found = True
> Exit For
> End If
> Next n
> If found = False Then
> combinationArray(level) = i
> If level = ArraySize Then
> If Mode = Forward Then
> 'check if search value was found
> found = True
> For j = 0 To ArraySize
> If SearchArray(j) <> _
> MyNumbers(combinationArray(j)) Then
> found = False
> Exit For
> End If
> Next j
> If found = True Then
> CombinationToIndex = index
> Exit For
> Else
> CombinationToIndex = -1
> End If
> Else
> If index >= LowerLimit Then
> For k = 0 To ArraySize
> If k = 0 Then
> PrintString = _
> CStr(MyNumbers(combinationArray(k)))
> Else
> PrintString = PrintString & "," & _
> CStr(MyNumbers(combinationArray(k)))
> End If
> Next k
> MsgBox (PrintString)
> If index = UpperLimit Then
> CombinationToIndex = 0
> Exit For
> Else
> CombinationToIndex = -1
> End If
> Else
> CombinationToIndex = -1
> End If
> End If
> index = index + 1
> Else
> CombinationToIndex = _
> CombinationToIndex(Mode, level + 1, index)
> End If
> If CombinationToIndex <> -1 Then
> Exit For
> End If
> End If
> Next i
>
> End Function
>
>
> "Dana DeLouis" wrote:
>
>> Hi. Here are your first 6 outputs:
>>
>>> 50,6,1,7,23,35,47,49
>>> 50,6,1,7,23,35,47,50
>>> 50,6,1,7,23,35,49,1
>>> 50,6,1,7,23,35,49,6
>>> 50,6,1,7,23,35,49,7
>>> 50,6,1,7,23,35,49,23
>> I may be wrong, but I think there is a logic error in the code.
>> I read the op's question as being that from a set of s=50, and Subsets of
>> size 6,
>> find the next Subset in Lexicographical order.
>> (Using another program, the next Subset appears correct:
>>
>> NextKSubset[s, {1, 7, 23, 35, 47, 49}]
>> {1, 7, 23, 35, 47, 50}
>>
>> But your third one is a little off.
>>
>> NextKSubset[s, %]
>> {1, 7, 23, 35, 48, 49}
>>
>> Also note that your 6th output has two "23's", which can not be part of a
>> Subset in this context.
>>
>> I believe the Op is asking for the "Rank"
>>
>> n = RankKSubset[{1, 7, 23, 35, 47, 49}, s] + 1
>>
>> 926,277
>>
>> (ie it's the 926,277 item in the list)
>>
>> and then wanting to "UnRank" it.
>>
>> UnrankKSubset[n, 6, s]
>>
>> {1, 7, 23, 35, 47, 50}
>>
>> Again, I may be wrong.
>>
>> --
>> Dana DeLouis
>>
>> <snip>
>>
>>

RankKSubset functions very well

I tested it with:
combin(20;3)
combin(20;4)
combin(20;5)
combin(20;6)

it is exactly what I wanted

thank you very much * X

I have another question about the combinations, if it is possible.

I make a new message or in that there


Dana DeLouis a écrit :
> Hi. Here's another way. Note that in your example, we assume the sizeof
> the subset from the given array of values. (we don't supply the '6).
> We do have to give the size of the set though.
>
> Sub TestYourData()
> '//Easy Test (1)
> Debug.Print RankKSubset(9, 1, 2, 3, 4, 5, 6)
>
> '// Other easy test...
> Debug.Print RankKSubset(9, 4, 5, 6, 7, 8, 9)
> Debug.Print [Combin(9,6)]
>
> Debug.Print RankKSubset(50, 1, 7, 23, 35, 47, 49)
>
> Debug.Print RankKSubset(20, 1, 2, 3, 4, 5, 19)
> Debug.Print RankKSubset(20, 1, 2, 3, 4, 5, 14)
> End Sub
>
> Function RankKSubset(Size, ParamArray v())
> '//= = = = = = = = = = = = = = = = = =
> '// By: Dana DeLouis
> '//= = = = = = = = = = = = = = = = = =
>
> Dim j As Long
> Dim d As Double
> Dim n As Double
> Dim m As Double
> Dim T As Double
>
> With WorksheetFunction
> m = Size
> n = UBound(v) + 1
> d = v(0)
> T = T + .Combin(m, n) - .Combin(m - d + 1, n)
> For j = 1 To UBound(v) - 1
> m = m - d
> n = n - 1
> d = v(j) - v(j - 1)
> T = T + .Combin(m, n) - .Combin(m - d + 1, n)
> Next j
> T = T + v(j) - v(j - 1)
> End With
> RankKSubset = T
> End Function
>
>
> To convert a number to a subset, we need to supply both the size of theset,
> and the size of the subset.
> So, in our example of 50 & 6, we note that if our number falls within
> =COMBIN(49,5) = 1906884
> then our first digit is 1.
> If n is greater than this, then we add =COMBIN(48,5) = 1712304
> If n falls within this new number, then the first number is 2...etc
> We have to do these short loops because there is no analytical way to
> directly solve the binomial sum (afaik)
>
> As a side note, when your size gets too large, it can sometimes be more
> efficient to call a NextPermutation routine
> I've done this before, but I can't find it at the moment.
> The reason for this is that if you had a size 1000, and a subset size of 14,
> then this exceeds Excel's extended capabilities.
> For example, if you had this size 14 subset out of 1,000:
>
> {2, 39, 140, 230, 262, 341, 443, 558, 612, 661, 674, 705, 804, 839}
>
> then it would be the
> 202,646,961,038,399,155,876,623,226,251
> 'th item in the list.
>
> This slightly exceeds Excel's extended capabilities.
> Hence, it is usually faster to work with the smaller array.
>

> I have another question about the combinations, if it is possible.

Glad it worked. :>)
Sure...What's your question?

--
Dana

"PST" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
RankKSubset functions very well

I tested it with:
combin(20;3)
combin(20;4)
combin(20;5)
combin(20;6)

it is exactly what I wanted

thank you very much * X

I have another question about the combinations, if it is possible.

<snip>

I have series of 1000 favorite combinations
pick 3 >>1000
pick 4 >>1000
pick 5 >>1000
pick 6 >>1000

ex: With the series of pick 3

I would like to convert them into combinations of 7 maximum

If I put value 7 in maximum,

the macro one must give me
_the combinations of 7 possible,
_then of 6,
_then of 5,
_then of 4,
_then of 3

If I put value 6 in maximum
_the combinations of 6 possible
_then of 5,
_then of 4,
_then of 3


the same combinations must be found in the modified combinations.

ex:
Comb 1: 1,2,3
Comb 2: 1,2,4
Comb 3: 1,2,5

the result a combination of 5

1,2,3,4,5

Comb 1: 1,2,3
Comb 2: 1,2,4
Comb 3: 1,2,5
Comb 3: 1,2,6

Another ex:
the result a combination of 6

1,2,3,4,5,6

The goal is to decrease the combinations but by finding about the same
ones.

I do not know if I were clearly

thank you


Dana DeLouis a écrit :
>> I have another question about the combinations, if it is possible.
>
> Glad it worked. :>)
> Sure...What's your question?
>