Line to Index -Index to Line

K

Kishan

Well-known Member
Joined
Mar 15, 2011
Messages
1,648
Office Version
  1. 2010
Platform
  1. Windows
Hello To All,</SPAN></SPAN>

Here is a formula that extract the index of any combination is filled in cells A3:F5 from 1 to 49 numbers in sequence of 6 numbers.</SPAN></SPAN>
Given formula G3 will be copied down to G5</SPAN></SPAN>


A</SPAN></SPAN>
B</SPAN></SPAN>
C</SPAN></SPAN>
D</SPAN></SPAN>
E</SPAN></SPAN>
F</SPAN></SPAN>
G</SPAN></SPAN>
1</SPAN></SPAN>
Index</SPAN>
2</SPAN></SPAN>
n1</SPAN></SPAN>
n2</SPAN></SPAN>
n3</SPAN></SPAN>
n4</SPAN></SPAN>
n5</SPAN></SPAN>
n6</SPAN></SPAN>
Combination Index</SPAN></SPAN>
3</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
3</SPAN></SPAN>
4</SPAN></SPAN>
5</SPAN></SPAN>
6</SPAN></SPAN>
1</SPAN></SPAN>
4</SPAN></SPAN>
1</SPAN></SPAN>
2</SPAN></SPAN>
3</SPAN></SPAN>
4</SPAN></SPAN>
5</SPAN></SPAN>
15</SPAN></SPAN>
10</SPAN></SPAN>
5</SPAN></SPAN>
44</SPAN></SPAN>
45</SPAN></SPAN>
46</SPAN></SPAN>
47</SPAN></SPAN>
48</SPAN></SPAN>
49</SPAN></SPAN>
13.983.816</SPAN></SPAN>

<TBODY>
[TD="colspan: 6"] Line To----></SPAN> </SPAN>
[/TD]

</TBODY>

Spreadsheet Formulas</SPAN>
Cell</SPAN></SPAN>
Formula</SPAN></SPAN>
G3</SPAN></SPAN>
=COMBIN(49,6)-IF(44-A3>0,COMBIN(49-A3,6),0)-IF(45-B3>0,COMBIN(49-B3,5),0)-IF(46-C3>0,COMBIN(49-C3,4),0)-IF(47-D3>0,COMBIN(49-D3,3),0)-IF(48-E3>0,COMBIN(49-E3,2),0)-IF(49-F3>0,COMBIN(49-F3,1),0)-0.000000002</SPAN></SPAN>

<TBODY>
</TBODY>


<TBODY>
</TBODY>


Now my request is there any formula or function</SPAN></SPAN>
If I enter in column A Index number then I can get corresponding combination in cells B3:G5 </SPAN></SPAN>


A</SPAN></SPAN>
B</SPAN></SPAN>
C</SPAN></SPAN>
D</SPAN></SPAN>
E</SPAN></SPAN>
F</SPAN></SPAN>
G</SPAN></SPAN>
1</SPAN></SPAN>
Index To-----></SPAN>
2</SPAN></SPAN>
Combination Index</SPAN></SPAN>
n1</SPAN></SPAN>
n2</SPAN></SPAN>
n3</SPAN></SPAN>
n4</SPAN></SPAN>
n5</SPAN></SPAN>
n6</SPAN></SPAN>
3</SPAN></SPAN>
1</SPAN></SPAN>





4</SPAN></SPAN>
10</SPAN></SPAN>





5</SPAN></SPAN>
13.983.816</SPAN></SPAN>






<TBODY>
[TD="colspan: 6"] Line</SPAN> </SPAN>
[/TD]

</TBODY>

Please Help</SPAN></SPAN>

Thanks In Advance,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.
Sort by date Sort by votes
Hello To All,</SPAN></SPAN>

In other world Index is the 6/49 lottery sequence where </SPAN></SPAN>
Sequence 1 represent to combination 1,2,3,4,5,6</SPAN></SPAN>
Sequence 10 represent to combination 1,2,3,4,5,15</SPAN></SPAN>
Sequence 13.983.816 represent to combination 44,45,46,47,48,49</SPAN></SPAN>

[TABLE="width: 352"]
<TBODY>[TR]
[TD]Index To-----></SPAN>
[/TD]
[TD="colspan: 6"]Line</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]Combination Index</SPAN>
[/TD]
[TD]n1</SPAN></SPAN>
[/TD]
[TD]n2</SPAN></SPAN>
[/TD]
[TD]n3</SPAN></SPAN>
[/TD]
[TD]n4</SPAN></SPAN>
[/TD]
[TD]n5</SPAN></SPAN>
[/TD]
[TD]n6</SPAN></SPAN>
[/TD]
[/TR]
[TR]
[TD]1</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]10</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13.983.816</SPAN></SPAN>
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</TBODY>[/TABLE]

So please need your help to have formula or UDF that can extract the combination of given sequence in 6 numbers.</SPAN></SPAN>

Thanks In Advance,</SPAN></SPAN>
Kishan</SPAN></SPAN>
 
Upvote 0
Hello To All,<o:p></o:p>
<o:p></o:p>
I found code which is below, that do the job extract the combination if I place in cell A1 the sequence.<o:p></o:p>
For example if I place 10 in cell A1 I get result 1,2,3,4,5,15 in Cells B1:G1 <o:p></o:p>
<o:p></o:p>
Rich (BB code):
Rich (BB code):
Rich (BB code): 
<o:p></o:p>
Rich (BB code): 
Option Explicit<o:p></o:p>
Dim A As Integer, B As Integer, C As Integer, D As Integer, E As Integer, F As Integer<o:p></o:p>
Dim nVal As Double, nLex As Double<o:p></o:p>
Sub LexToNumbers_For_6_49_Game()<o:p></o:p>
nVal = Range("A1").Value<o:p></o:p>
nLex = 0<o:p></o:p>
For A = 1 To 44<o:p></o:p>
For B = A + 1 To 45<o:p></o:p>
For C = B + 1 To 46<o:p></o:p>
For D = C + 1 To 47<o:p></o:p>
For E = D + 1 To 48<o:p></o:p>
For F = E + 1 To 49<o:p></o:p>
 <o:p></o:p>
nLex = nLex + 1<o:p></o:p>
If nLex = nVal Then<o:p></o:p>
Range("B1").Value = A<o:p></o:p>
Range("C1").Value = B<o:p></o:p>
Range("D1").Value = C<o:p></o:p>
Range("E1").Value = D<o:p></o:p>
Range("F1").Value = E<o:p></o:p>
Range("G1").Value = F<o:p></o:p>
 <o:p></o:p>
Exit Sub<o:p></o:p>
End If<o:p></o:p>
 <o:p></o:p>
Next F<o:p></o:p>
Next E<o:p></o:p>
Next D<o:p></o:p>
Next C<o:p></o:p>
Next B<o:p></o:p>
Next A<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
My request is it possible to modified code if I place sequence in column cells A1:A100, so that I get all 100 set combination in cells B1:G100 at a time.<o:p></o:p>
<o:p></o:p>
Please help<o:p></o:p>
<o:p></o:p>
Thanks In Advance,<o:p></o:p>
Kishan
 
Upvote 0
Hello To All,<o:p></o:p>
<o:p></o:p>
Here is UDF<o:p></o:p>
<o:p></o:p>
Select and highlight the cells A1,B1,C1,D1,E1 In the formula bar type in the formula:
=Lex_2_Num(G1)

Do NOT press ENTER. Instead press Ctrl-Shift-Enter. The reason for this is that the formula is an Array formula which I mentioned earlier. You have to confirm Array formulas by pressing Ctrl-Shift-Enter instead of just Enter.

UDF Code:
Code: 
Function Lex_2_Num(LexVal As Long)
Dim A As Long
Dim B As Long
Dim C As Long
Dim D As Long
Dim E As Long
Dim n As Long
With Application
.ScreenUpdating = False: .Calculation = xlCalculationManual: .DisplayAlerts = False
End With
If LexVal < 1 Or LexVal > 575757 Then Exit Function
For A = 1 To 35
For B = A + 1 To 36
For C = B + 1 To 37
For D = C + 1 To 38
For E = D + 1 To 39
n = n + 1
If n = LexVal Then
Lex_2_Num = Array(A, B, C, D, E)
Exit Function
End If
Next
Next
Next
Next
Next
With Application
.DisplayAlerts = True: .Calculation = xlCalculationAutomatic: .ScreenUpdating = True
End With
End Function<o:p></o:p>[/FONT][/SIZE][/COLOR]
[COLOR=black][SIZE=3][FONT=Times New Roman]

Now when change the Lexicographic Index Number in cell G1 it will produce the combination in cells A1,B1,C1,D1,E1 relevant to that Lexicographic Index Number.

<o:p></o:p>
But still I need help to have VBA code with loop <o:p></o:p>
<o:p></o:p>
Thanks And Regards,<o:p></o:p>
Kishan<o:p></o:p>
 
Upvote 0
Hello To All,<o:p></o:p>
<o:p></o:p>
I found one link in this web, which is very useful (UDF written By Andrew Fergus)<o:p></o:p>
<o:p></o:p>
http://www.mrexcel.com/forum/excel-questions/287099-lexigraphic-lottery-values.html#post1410474<o:p></o:p>
<o:p></o:p>
Rich (BB code): 
<o:p></o:p>
Rich (BB code):
Rich (BB code):
Rich (BB code): 
Public Function DeriveCombo(Population As Long, Sample As Long, ComboNumber As Long) As String<o:p></o:p>
 <o:p></o:p>
Dim LoopCount As Long, PositionNum As Long, LastPosNumber As Long<o:p></o:p>
Dim Remainder As Long, tempVal As Long<o:p></o:p>
 <o:p></o:p>
LastPosNumber = 0<o:p></o:p>
PositionNum = 0<o:p></o:p>
Remainder = WorksheetFunction.Combin(Population, Sample)<o:p></o:p>
'Debug.Print Remainder; ComboNumber<o:p></o:p>
Do Until Remainder = ComboNumber<o:p></o:p>
    For LoopCount = LastPosNumber + 1 To Population<o:p></o:p>
        tempVal = WorksheetFunction.Combin(Population - LoopCount, Sample - PositionNum)<o:p></o:p>
        If CLng(Remainder - tempVal) >= ComboNumber Then<o:p></o:p>
            If PositionNum = 0 Then<o:p></o:p>
                DeriveCombo = LoopCount<o:p></o:p>
            Else<o:p></o:p>
                DeriveCombo = DeriveCombo & "," & LoopCount<o:p></o:p>
            End If<o:p></o:p>
            PositionNum = PositionNum + 1<o:p></o:p>
            LastPosNumber = LoopCount<o:p></o:p>
            Remainder = CLng(Remainder - tempVal)<o:p></o:p>
            'Debug.Print PositionNum; LastPosNumber; LoopCount; tempVal; Remainder<o:p></o:p>
            LoopCount = Population<o:p></o:p>
        End If<o:p></o:p>
    Next LoopCount<o:p></o:p>
    If Remainder = ComboNumber And PositionNum = Sample - 1 Then<o:p></o:p>
        DeriveCombo = DeriveCombo & "," & Population<o:p></o:p>
    End If<o:p></o:p>
Loop<o:p></o:p>
 <o:p></o:p>
End Function<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
Formula: =DeriveCombo(49,6,10) to get the 10th combination 1,2,3,4,5,15<o:p></o:p>
<o:p></o:p>
With this Function it’s possible to adapt the different pool numbers for example 5/56 ,5/36 ,5/39 or 6/49 etc. and simply changing the value in cells according to their lotteries in my example in cells D and E without modifying the code for any lottery.<o:p></o:p>
It’s simply just entering any sequence in column A cells and you ge6t digit combinations in cells B, which correspond to their sequence. <o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>
<o:p></o:p>​
A<o:p></o:p>​
B<o:p></o:p>​
C<o:p></o:p>​
D<o:p></o:p>​
E<o:p></o:p>​
1<o:p></o:p>​
5<o:p></o:p>​
56<o:p></o:p>​
2<o:p></o:p>​
3<o:p></o:p>​
4<o:p></o:p>​
5<o:p></o:p>​

<TBODY> [TD="bgcolor: transparent"]
1<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
1,2,3,4,5<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
16<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
1,2,3,4,20<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
27<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
1,2,3,4,31<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
23504<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
1,2,36,38,49<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"]
3.819.812<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"]
51,52,53,54,56<o:p></o:p>​
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] <o:p></o:p>
[/TD]

</TBODY>
<o:p></o:p>

<TBODY>
[TD="bgcolor: transparent"] Spreadsheet Formulas <o:p></o:p>
[/TD]

[TD="bgcolor: transparent"]
Cell<o:p></o:p>
Formula<o:p></o:p>

<TBODY>
[TD="bgcolor: transparent"] B1<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] =DeriveCombo($E$1;$D$1;A1)<o:p></o:p>
[/TD]

[TD="bgcolor: transparent"] B2<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] =DeriveCombo($E$1;$D$1;A2)<o:p></o:p>
[/TD]

[TD="bgcolor: transparent"] B3<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] =DeriveCombo($E$1;$D$1;A3)<o:p></o:p>
[/TD]

[TD="bgcolor: transparent"] B4<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] =DeriveCombo($E$1;$D$1;A4)<o:p></o:p>
[/TD]

[TD="bgcolor: transparent"] B5<o:p></o:p>
[/TD]
[TD="bgcolor: transparent"] =DeriveCombo($E$1;$D$1;A5)<o:p></o:p>
[/TD]

</TBODY>
<o:p></o:p>
[/TD]

</TBODY>
<o:p></o:p>
<o:p></o:p>
Hope this will help to someone, <o:p></o:p>
<o:p></o:p>
Regards,<o:p></o:p>
Kishan<o:p></o:p>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
2K
montex01
M
C
  • Solved
Formula assistance - identifying full or part counts
Replies
5
Views
276
RobP
R
K
  • Solved
Index with Multiple Conditions
Replies
2
Views
105
kivikatz
K
T
  • Question Question
Find highest scorers and return their name
2
Replies
13
Views
218
Peter_SSs
Peter_SSs
X
  • Question Question
How To Use Probability Function With Multiple Crieterias?
Replies
0
Views
312
xlmaniac
X

Forum statistics

Threads
1,224,527
Messages
6,179,331
Members
452,907
Latest member
Roland Deschain

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top