Welcome Guest
Log In | Register )
You last visited January 16, 2017, 9:40 pm
All times shown are
Eastern Time (GMT-5:00)

Excel help need Combination FROM Lexicographix number

Topic closed. 26 replies. Last post 6 years ago by RL-RANDOMLOGIC.

Page 1 of 2
51
PrintE-mailLink
frenchie's avatar - Lottery-041.jpg
Los Angeles
United States
Member #75410
June 2, 2009
489 Posts
Offline
Posted: August 31, 2010, 4:08 pm - IP Logged

Hi every one,

        What formula will be needed to get a combination of 5/56 FROM the lexicographic number ?

Let say I have in cell : A1 the last lexicographic number 3819816.

What formula would be needed to create the combination in cell : C1,D1,E1,F1,G1.  ?

 

                                                                                                                                    THANK YOU ALL

    Raven62's avatar - binary
    New Jersey
    United States
    Member #17843
    June 28, 2005
    50995 Posts
    Offline
    Posted: August 31, 2010, 11:27 pm - IP Logged

    Hi every one,

            What formula will be needed to get a combination of 5/56 FROM the lexicographic number ?

    Let say I have in cell : A1 the last lexicographic number 3819816.

    What formula would be needed to create the combination in cell : C1,D1,E1,F1,G1.  ?

     

                                                                                                                                        THANK YOU ALL

    http://www.lotterypost.com/thread/217450/1711409

    A mind once stretched by a new idea never returns to its original dimensions!

      frenchie's avatar - Lottery-041.jpg
      Los Angeles
      United States
      Member #75410
      June 2, 2009
      489 Posts
      Offline
      Posted: September 1, 2010, 2:24 am - IP Logged

      Thank you Raven62,

            I know I had it posted before but the answer was with a macro, and not a FORMULA, so if someone can help, it would be highly appreciated, I need a formula or formulas if posible.

       

                                                                                                                        Thank you.

        frenchie's avatar - Lottery-041.jpg
        Los Angeles
        United States
        Member #75410
        June 2, 2009
        489 Posts
        Offline
        Posted: September 2, 2010, 7:46 pm - IP Logged

        Anyone ? please   Thanks.

          JADELottery's avatar - Inky Move-01.gif
          The Quantum Master
          West Concord, MN
          United States
          Member #21
          December 7, 2001
          3680 Posts
          Online
          Posted: September 4, 2010, 4:36 pm - IP Logged

          working on it, but we call it a Combinatorial Index.

          we have it going from a Combination to an Index.

          now we have to set it up for an Index to a Combination.

          Presented 'AS IS' and for Entertainment Purposes Only.
          Any gain or loss is your responsibility.
          Use at your own risk.

          Order is a Subset of Chaos
          Knowledge is Beyond Belief
          Wisdom is Not Censored
          Douglas Paul Smallish
          Jehocifer

            frenchie's avatar - Lottery-041.jpg
            Los Angeles
            United States
            Member #75410
            June 2, 2009
            489 Posts
            Offline
            Posted: September 4, 2010, 6:46 pm - IP Logged

            Hi JADE,

                            Thank you for your prompt respond, I look forward to hearing from you.

              JADELottery's avatar - Inky Move-01.gif
              The Quantum Master
              West Concord, MN
              United States
              Member #21
              December 7, 2001
              3680 Posts
              Online
              Posted: September 4, 2010, 9:52 pm - IP Logged

              Ok, Here It Is.

              You can download the Excel file at ftp://www.jadexcode.com/excel

              The file name is CombinatorialIndex.xls

              The VB Macro Source Code is shown below

              Function Fact(N As Integer) As Double
                  If (N < 1) Then
                      Fact = 1
                  Else
                      Fact = N * Fact(N - 1)
                  End If
              End Function

              Function Perm(N As Integer, R As Integer) As Double
                  If (N < R) Then
                      Perm = 0
                  Else
                      Perm = Fact(N) / Fact(N - R)
                  End If
              End Function

              Function Comb(N As Integer, R As Integer) As Double
                  If (N < R) Then
                      Comb = 0
                  Else
                      Comb = Perm(N, R) / Fact(R)
                  End If
              End Function

              Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Double
                  If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
                      Cdist = 0
                  Else
                      Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
                  End If
              End Function

              Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Double
                  Dim a As Integer
                 
                  If Z < 1 Then
                      fColumnSum = 0
                  ElseIf (Z >= 1) And (Z < N - R + 1) Then
                      col_sum = 0
                      For a = 1 To Z
                          col_sum = col_sum + Cdist(N, R, 1, a)
                      Next a
                      fColumnSum = col_sum
                  ElseIf Z >= N - R + 1 Then
                      fColumnSum = Comb(N, R)
                  End If
              End Function

              Function Index2Combin(N As Integer, R As Integer, I As Double) As String

                  Dim Combination(), Z As Integer
                  ReDim Combination(R)
                  Dim tmpString As String
                  Dim NumberFound As Boolean
                 
                  tmpString = ""
                  I = I - 1
                  Z = 0
                 
                  For a = 1 To R
                 
                      If I <= Comb(N, R) Then
                          If a = 1 Then
                              Combination(a) = 1
                          Else
                              Combination(a) = Combination(a - 1) + 1
                          End If
                     
                          NumberFound = False
                     
                          Do
                     
                              Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
                                  Case Is < 0
                                      Combination(a) = Combination(a) - 1
                                      NumberFound = True
                                  Case Is = 0
                                      NumberFound = True
                                  Case Is > 0
                                      Combination(a) = Combination(a) + 1
                              End Select
                     
                          Loop Until NumberFound
                     
                          I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
                          Z = Combination(a)
                         
                      Else
                     
                          Combination(a) = 0
                         
                      End If
                     
                      tmpString = tmpString & Format(Combination(a), "00")
                      If a < R Then tmpString = tmpString & " "
                 
                  Next a
                 
                  Index2Combin = tmpString

              End Function

              Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Double
                 
                  Dim a As Integer
                  Dim fSum As Double
                  Dim NotInAscendingOrder, NotInPool As Boolean
                  NotInAscendingOrder = False
                  NotInPool = False
                 
                  If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
                      Combin2Index = -1
                      Exit Function
                  End If
                 
                  For a = 1 To R
                      If a < R Then
                          If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
                              NotInAscendingOrder = True
                          End If
                      End If
                     
                      If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
                          NotInPool = True
                      End If
                  Next a
                 
                  If NotInAscendingOrder Or NotInPool Then
                      Combin2Index = -1
                      Exit Function
                  End If
                 
                 
                  fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
                  For a = 2 To R
                      fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
                  Next a
                  Combin2Index = fSum
                 
              End Function

              Presented 'AS IS' and for Entertainment Purposes Only.
              Any gain or loss is your responsibility.
              Use at your own risk.

              Order is a Subset of Chaos
              Knowledge is Beyond Belief
              Wisdom is Not Censored
              Douglas Paul Smallish
              Jehocifer

                JADELottery's avatar - Inky Move-01.gif
                The Quantum Master
                West Concord, MN
                United States
                Member #21
                December 7, 2001
                3680 Posts
                Online
                Posted: September 4, 2010, 11:22 pm - IP Logged

                Corrected a Data Type glitch, but could have some limitations on very large Combinatorial Sets.

                Play around with the Data Types on different programming platforms.

                _________________________________________

                Function Fact(N As Integer) As Double
                    If (N < 1) Then
                        Fact = 1
                    Else
                        Fact = N * Fact(N - 1)
                    End If
                End Function

                Function Perm(N As Integer, R As Integer) As Double
                    If (N < R) Then
                        Perm = 0
                    Else
                        Perm = Fact(N) / Fact(N - R)
                    End If
                End Function

                Function Comb(N As Integer, R As Integer) As Long
                    If (N < R) Then
                        Comb = 0
                    Else
                        Comb = Perm(N, R) / Fact(R)
                    End If
                End Function

                Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Long
                    If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
                        Cdist = 0
                    Else
                        Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
                    End If
                End Function

                Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Long
                    Dim a As Integer
                   
                    If Z < 1 Then
                        fColumnSum = 0
                    ElseIf (Z >= 1) And (Z < N - R + 1) Then
                        col_sum = 0
                        For a = 1 To Z
                            col_sum = col_sum + Cdist(N, R, 1, a)
                        Next a
                        fColumnSum = col_sum
                    ElseIf Z >= N - R + 1 Then
                        fColumnSum = Comb(N, R)
                    End If
                End Function

                Function Index2Combin(N As Integer, R As Integer, I As Double) As String

                    Dim Combination(), Z As Integer
                    ReDim Combination(R)
                    Dim tmpString As String
                    Dim NumberFound As Boolean
                   
                    tmpString = ""
                    I = I - 1
                    Z = 0
                   
                    For a = 1 To R
                   
                        If I <= Comb(N, R) Then
                            If a = 1 Then
                                Combination(a) = 1
                            Else
                                Combination(a) = Combination(a - 1) + 1
                            End If
                       
                            NumberFound = False
                       
                            Do
                       
                                Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
                                    Case Is < 0
                                        Combination(a) = Combination(a) - 1
                                        NumberFound = True
                                    Case Is = 0
                                        NumberFound = True
                                    Case Is > 0
                                        Combination(a) = Combination(a) + 1
                                End Select
                       
                            Loop Until NumberFound
                       
                            I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
                            Z = Combination(a)
                           
                        Else
                       
                            Combination(a) = 0
                           
                        End If
                       
                        tmpString = tmpString & Format(Combination(a), "00")
                        If a < R Then tmpString = tmpString & " "
                   
                    Next a
                   
                    Index2Combin = tmpString

                End Function

                Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Long
                   
                    Dim a As Integer
                    Dim fSum As Double
                    Dim NotInAscendingOrder, NotInPool As Boolean
                    NotInAscendingOrder = False
                    NotInPool = False
                   
                    If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
                        Combin2Index = -1
                        Exit Function
                    End If
                   
                    For a = 1 To R
                        If a < R Then
                            If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
                                NotInAscendingOrder = True
                            End If
                        End If
                       
                        If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
                            NotInPool = True
                        End If
                    Next a
                   
                    If NotInAscendingOrder Or NotInPool Then
                        Combin2Index = -1
                        Exit Function
                    End If
                   
                   
                    fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
                    For a = 2 To R
                        fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
                    Next a
                    Combin2Index = fSum
                   
                End Function

                Presented 'AS IS' and for Entertainment Purposes Only.
                Any gain or loss is your responsibility.
                Use at your own risk.

                Order is a Subset of Chaos
                Knowledge is Beyond Belief
                Wisdom is Not Censored
                Douglas Paul Smallish
                Jehocifer

                  frenchie's avatar - Lottery-041.jpg
                  Los Angeles
                  United States
                  Member #75410
                  June 2, 2009
                  489 Posts
                  Offline
                  Posted: September 5, 2010, 1:38 am - IP Logged

                     Thank you very much JADE, for your extensive work ,I'm impressed.

                      But one question : this is the second time I'm asking for a Excel formula, and both time I've got answered with a macro solution, is that mean that it's not posible to get the result with a formula ?

                      For the time been, I will change my file and use your macro instead.

                   

                                                                                                                     Thank you JADE...              Regards.

                    JADELottery's avatar - Inky Move-01.gif
                    The Quantum Master
                    West Concord, MN
                    United States
                    Member #21
                    December 7, 2001
                    3680 Posts
                    Online
                    Posted: September 5, 2010, 9:17 am - IP Logged

                    i know what you are looking for, but i've not had much time to look into the problem any further.

                    actually, if you look carefully at the code, you could find a solution to your problem.

                    what you see is how far we got and we are also looking for the same single function.

                    i'm sure there is a simple solution, however we like to take the long way around to finding the solution.

                    we learn a lot more that way.

                    Presented 'AS IS' and for Entertainment Purposes Only.
                    Any gain or loss is your responsibility.
                    Use at your own risk.

                    Order is a Subset of Chaos
                    Knowledge is Beyond Belief
                    Wisdom is Not Censored
                    Douglas Paul Smallish
                    Jehocifer

                      Raven62's avatar - binary
                      New Jersey
                      United States
                      Member #17843
                      June 28, 2005
                      50995 Posts
                      Offline
                      Posted: September 5, 2010, 9:53 am - IP Logged

                      Thank you Raven62,

                            I know I had it posted before but the answer was with a macro, and not a FORMULA, so if someone can help, it would be highly appreciated, I need a formula or formulas if posible.

                       

                                                                                                                                        Thank you.

                      It would be nice if it was as easy as this:

                      Cell B2...........................Cell C2 thru Cell G2
                      =CSN(3819816,5,56) = 52 53 54 55 56

                      A mind once stretched by a new idea never returns to its original dimensions!

                        JADELottery's avatar - Inky Move-01.gif
                        The Quantum Master
                        West Concord, MN
                        United States
                        Member #21
                        December 7, 2001
                        3680 Posts
                        Online
                        Posted: September 5, 2010, 10:50 am - IP Logged

                        Corrected a Data Type glitch, but could have some limitations on very large Combinatorial Sets.

                        Play around with the Data Types on different programming platforms.

                        _________________________________________

                        Function Fact(N As Integer) As Double
                            If (N < 1) Then
                                Fact = 1
                            Else
                                Fact = N * Fact(N - 1)
                            End If
                        End Function

                        Function Perm(N As Integer, R As Integer) As Double
                            If (N < R) Then
                                Perm = 0
                            Else
                                Perm = Fact(N) / Fact(N - R)
                            End If
                        End Function

                        Function Comb(N As Integer, R As Integer) As Long
                            If (N < R) Then
                                Comb = 0
                            Else
                                Comb = Perm(N, R) / Fact(R)
                            End If
                        End Function

                        Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Long
                            If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
                                Cdist = 0
                            Else
                                Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
                            End If
                        End Function

                        Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Long
                            Dim a As Integer
                           
                            If Z < 1 Then
                                fColumnSum = 0
                            ElseIf (Z >= 1) And (Z < N - R + 1) Then
                                col_sum = 0
                                For a = 1 To Z
                                    col_sum = col_sum + Cdist(N, R, 1, a)
                                Next a
                                fColumnSum = col_sum
                            ElseIf Z >= N - R + 1 Then
                                fColumnSum = Comb(N, R)
                            End If
                        End Function

                        Function Index2Combin(N As Integer, R As Integer, I As Double) As String

                            Dim Combination(), Z As Integer
                            ReDim Combination(R)
                            Dim tmpString As String
                            Dim NumberFound As Boolean
                           
                            tmpString = ""
                            I = I - 1
                            Z = 0
                           
                            For a = 1 To R
                           
                                If I <= Comb(N, R) Then
                                    If a = 1 Then
                                        Combination(a) = 1
                                    Else
                                        Combination(a) = Combination(a - 1) + 1
                                    End If
                               
                                    NumberFound = False
                               
                                    Do
                               
                                        Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
                                            Case Is < 0
                                                Combination(a) = Combination(a) - 1
                                                NumberFound = True
                                            Case Is = 0
                                                NumberFound = True
                                            Case Is > 0
                                                Combination(a) = Combination(a) + 1
                                        End Select
                               
                                    Loop Until NumberFound
                               
                                    I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
                                    Z = Combination(a)
                                   
                                Else
                               
                                    Combination(a) = 0
                                   
                                End If
                               
                                tmpString = tmpString & Format(Combination(a), "00")
                                If a < R Then tmpString = tmpString & " "
                           
                            Next a
                           
                            Index2Combin = tmpString

                        End Function

                        Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Long
                           
                            Dim a As Integer
                            Dim fSum As Double
                            Dim NotInAscendingOrder, NotInPool As Boolean
                            NotInAscendingOrder = False
                            NotInPool = False
                           
                            If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
                                Combin2Index = -1
                                Exit Function
                            End If
                           
                            For a = 1 To R
                                If a < R Then
                                    If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
                                        NotInAscendingOrder = True
                                    End If
                                End If
                               
                                If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
                                    NotInPool = True
                                End If
                            Next a
                           
                            If NotInAscendingOrder Or NotInPool Then
                                Combin2Index = -1
                                Exit Function
                            End If
                           
                           
                            fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
                            For a = 2 To R
                                fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
                            Next a
                            Combin2Index = fSum
                           
                        End Function

                        FYI, the Data Types are the Double, Long, Integer, and String parts of the code.

                        the Excel file is setup and ready to be manipulated as you see fit, however you need to enable macros when using it.

                        if you don't want to download the file, then copy and paste the posted code here into your VB macro editor.

                        we recommend looking over the Excel file though, because the file has some functions that can break up the returned string value in the =Index2Combin(N, R, Index) function.

                        Presented 'AS IS' and for Entertainment Purposes Only.
                        Any gain or loss is your responsibility.
                        Use at your own risk.

                        Order is a Subset of Chaos
                        Knowledge is Beyond Belief
                        Wisdom is Not Censored
                        Douglas Paul Smallish
                        Jehocifer

                          winsumloosesum's avatar - Lottery-060.jpg
                          Pennsylvania
                          United States
                          Member #2218
                          September 1, 2003
                          5396 Posts
                          Offline
                          Posted: September 5, 2010, 11:09 am - IP Logged

                          Jade this is excellent.  Thanks for the share.

                          RL also sent me excellent information also. 

                          I always wanted to know how to do this.

                           

                          This may help. 

                          I updated Jadelottery's Excel file "CombinatorialIndex.xls"

                          Here is the download link: http://www.box.net/shared/hxo736heiq

                          Look at the image below.

                          Step #1 - I added the highest value or the maximum number of balls drawn.  For example I entered a 49 in cell L1

                          Step #2 - I added the highest value for the total numal of balls drawn in cell L2.  For example I entered a 6 in cell L2

                          In cell L3 there is a formula that calculates the highest Combinatorial Index Number as JadeLottery calls it or Lexicogrphic Number or Combinational Sequence Number or whatever name you want to call it.

                          In Step # 3 - You just need to enter the Combinatorial Index Number, Lexicogrphic Number, Combinational Sequence Number etc.  The value must be any from 1 to the highest value in cell L3.

                          Frenchie all you need to do is just enter your lottery's values in cells L1 and L2 and then whatever value (Lexi, CSN, etc.) starting in cell A10.  You can drag all the formulas down.

                          You can change the value in Cell L1 to a 39 and change the value in cell L2 to a 5 for a 5/39 Matrix Lottery game then enter your values starting in cell A10.

                            frenchie's avatar - Lottery-041.jpg
                            Los Angeles
                            United States
                            Member #75410
                            June 2, 2009
                            489 Posts
                            Offline
                            Posted: September 6, 2010, 1:12 am - IP Logged

                            JADE and Winsum,

                                           Thank you so much to both of you, now I've got what I wanted, this is working perfectly for what I do.

                             

                                                                                                                                     Regards.

                              JADELottery's avatar - Inky Move-01.gif
                              The Quantum Master
                              West Concord, MN
                              United States
                              Member #21
                              December 7, 2001
                              3680 Posts
                              Online
                              Posted: September 6, 2010, 11:13 am - IP Logged

                              Ok, Here It Is.

                              You can download the Excel file at ftp://www.jadexcode.com/excel

                              The file name is CombinatorialIndex.xls

                              The VB Macro Source Code is shown below

                              Function Fact(N As Integer) As Double
                                  If (N < 1) Then
                                      Fact = 1
                                  Else
                                      Fact = N * Fact(N - 1)
                                  End If
                              End Function

                              Function Perm(N As Integer, R As Integer) As Double
                                  If (N < R) Then
                                      Perm = 0
                                  Else
                                      Perm = Fact(N) / Fact(N - R)
                                  End If
                              End Function

                              Function Comb(N As Integer, R As Integer) As Double
                                  If (N < R) Then
                                      Comb = 0
                                  Else
                                      Comb = Perm(N, R) / Fact(R)
                                  End If
                              End Function

                              Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Double
                                  If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
                                      Cdist = 0
                                  Else
                                      Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
                                  End If
                              End Function

                              Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Double
                                  Dim a As Integer
                                 
                                  If Z < 1 Then
                                      fColumnSum = 0
                                  ElseIf (Z >= 1) And (Z < N - R + 1) Then
                                      col_sum = 0
                                      For a = 1 To Z
                                          col_sum = col_sum + Cdist(N, R, 1, a)
                                      Next a
                                      fColumnSum = col_sum
                                  ElseIf Z >= N - R + 1 Then
                                      fColumnSum = Comb(N, R)
                                  End If
                              End Function

                              Function Index2Combin(N As Integer, R As Integer, I As Double) As String

                                  Dim Combination(), Z As Integer
                                  ReDim Combination(R)
                                  Dim tmpString As String
                                  Dim NumberFound As Boolean
                                 
                                  tmpString = ""
                                  I = I - 1
                                  Z = 0
                                 
                                  For a = 1 To R
                                 
                                      If I <= Comb(N, R) Then
                                          If a = 1 Then
                                              Combination(a) = 1
                                          Else
                                              Combination(a) = Combination(a - 1) + 1
                                          End If
                                     
                                          NumberFound = False
                                     
                                          Do
                                     
                                              Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
                                                  Case Is < 0
                                                      Combination(a) = Combination(a) - 1
                                                      NumberFound = True
                                                  Case Is = 0
                                                      NumberFound = True
                                                  Case Is > 0
                                                      Combination(a) = Combination(a) + 1
                                              End Select
                                     
                                          Loop Until NumberFound
                                     
                                          I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
                                          Z = Combination(a)
                                         
                                      Else
                                     
                                          Combination(a) = 0
                                         
                                      End If
                                     
                                      tmpString = tmpString & Format(Combination(a), "00")
                                      If a < R Then tmpString = tmpString & " "
                                 
                                  Next a
                                 
                                  Index2Combin = tmpString

                              End Function

                              Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Double
                                 
                                  Dim a As Integer
                                  Dim fSum As Double
                                  Dim NotInAscendingOrder, NotInPool As Boolean
                                  NotInAscendingOrder = False
                                  NotInPool = False
                                 
                                  If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
                                      Combin2Index = -1
                                      Exit Function
                                  End If
                                 
                                  For a = 1 To R
                                      If a < R Then
                                          If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
                                              NotInAscendingOrder = True
                                          End If
                                      End If
                                     
                                      If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
                                          NotInPool = True
                                      End If
                                  Next a
                                 
                                  If NotInAscendingOrder Or NotInPool Then
                                      Combin2Index = -1
                                      Exit Function
                                  End If
                                 
                                 
                                  fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
                                  For a = 2 To R
                                      fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
                                  Next a
                                  Combin2Index = fSum
                                 
                              End Function

                              We've made one more tweak to the code.

                              This should get larger combination sets, but be careful.

                              We're not sure how well it works with larger sets beyond about Pick 10; maybe less.

                              You can download the Excel file at the following:

                              File - ftp://www.jadexcode.com/excel/combinatorialindex.xls

                              or

                              Folder - ftp://www.jadexcode.com/excel

                              If you would like just the code, it's posted below.

                              The VB Macro Source Code

                              Function Fact(N As Integer) As Double
                                  If (N <= 1) Then
                                      Fact = 1
                                  Else
                                      Fact = N * Fact(N - 1)
                                  End If
                              End Function

                              Function Perm(N As Integer, R As Integer) As Double
                                  Dim a As Integer
                                  Dim b As Double
                                  b = 1
                                  If (N < R) Then
                                      Perm = 0
                                  Else
                                      For a = N - R + 1 To N
                                          b = b * a
                                      Next a
                                      Perm = b
                                  End If
                              End Function

                              Function Comb(N As Integer, R As Integer) As Double
                                  If (N < R) Then
                                      Comb = 0
                                  Else
                                      Comb = Perm(N, R) / Fact(R)
                                  End If
                              End Function

                              Function Cdist(N As Integer, R As Integer, C As Integer, Z As Integer) As Double
                                  If (Z < C) Or (Z > (N - R + C)) Or (Z > N) Or (C > R) Or (N < 1) Or (R < 1) Or (C < 1) Or (Z < 1) Then
                                      Cdist = 0
                                  Else
                                      Cdist = Comb(Z - 1, C - 1) * Comb(N - Z, R - C)
                                  End If
                              End Function

                              Function fColumnSum(N As Integer, R As Integer, Z As Integer) As Double
                                  Dim a As Integer
                                 
                                  If Z < 1 Then
                                      fColumnSum = 0
                                  ElseIf (Z >= 1) And (Z < N - R + 1) Then
                                      col_sum = 0
                                      For a = 1 To Z
                                          col_sum = col_sum + Cdist(N, R, 1, a)
                                      Next a
                                      fColumnSum = col_sum
                                  ElseIf Z >= N - R + 1 Then
                                      fColumnSum = Comb(N, R)
                                  End If
                              End Function

                              Function Index2Combin(N As Integer, R As Integer, I As Double) As String

                                  Dim a, Combination(), Z As Integer
                                  ReDim Combination(R)
                                  Dim tmpString As String
                                  Dim NumberFound As Boolean
                                 
                                  tmpString = ""
                                  I = I - 1
                                  Z = 0
                                 
                                  For a = 1 To R
                                 
                                      If I <= (Comb(N, R) - 1) Then
                                          If a = 1 Then
                                              Combination(a) = 1
                                          Else
                                              Combination(a) = Combination(a - 1) + 1
                                          End If
                                     
                                          NumberFound = False
                                     
                                          Do
                                     
                                              Select Case (I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1))
                                                  Case Is < 0
                                                      Combination(a) = Combination(a) - 1
                                                      NumberFound = True
                                                  Case Is = 0
                                                      NumberFound = True
                                                  Case Is > 0
                                                      Combination(a) = Combination(a) + 1
                                              End Select
                                     
                                          Loop Until NumberFound
                                     
                                          I = I - fColumnSum(N - Z, R - (a - 1), Combination(a) - Z - 1)
                                          Z = Combination(a)
                                         
                                      Else
                                     
                                          Combination(a) = 0
                                         
                                      End If
                                     
                                      tmpString = tmpString & Format(Combination(a), "00")
                                      If a < R Then tmpString = tmpString & " "
                                 
                                  Next a
                                 
                                  Index2Combin = tmpString

                              End Function

                              Function Combin2Index(N As Integer, R As Integer, theRange As Range) As Double
                                 
                                  Dim a As Integer
                                  Dim fSum As Double
                                  Dim NotInAscendingOrder, NotInPool As Boolean
                                  NotInAscendingOrder = False
                                  NotInPool = False
                                 
                                  If (theRange.Rows.Count <> 1) Or (theRange.Columns.Count <> R) Then
                                      Combin2Index = -1
                                      Exit Function
                                  End If
                                 
                                  For a = 1 To R
                                      If a < R Then
                                          If theRange.Cells(1, a) >= theRange.Cells(1, a + 1) Then
                                              NotInAscendingOrder = True
                                          End If
                                      End If
                                     
                                      If (theRange.Cells(1, a) < 1) Or (theRange.Cells(1, a) > N) Then
                                          NotInPool = True
                                      End If
                                  Next a
                                 
                                  If NotInAscendingOrder Or NotInPool Then
                                      Combin2Index = -1
                                      Exit Function
                                  End If
                                 
                                 
                                  fSum = fColumnSum(N, R, theRange.Cells(1, 1) - 1) + 1
                                  For a = 2 To R
                                      fSum = fSum + fColumnSum(N - theRange.Cells(1, a - 1), R - a + 1, theRange.Cells(1, a) - theRange.Cells(1, a - 1) - 1)
                                  Next a
                                  Combin2Index = fSum
                                 
                              End Function

                              Presented 'AS IS' and for Entertainment Purposes Only.
                              Any gain or loss is your responsibility.
                              Use at your own risk.

                              Order is a Subset of Chaos
                              Knowledge is Beyond Belief
                              Wisdom is Not Censored
                              Douglas Paul Smallish
                              Jehocifer