Lexicographic Index Numbers / Combination Sequence Numbers (CSN's) for a Lotto.

[PAB]

Hi Serge,

It's not that I didn't want to go with VBA it's just that I have no knowledge what so ever about it, I know they are faster and can handle huge task.

The thing with the Function that I wrote for you is that it is EXTREMELY flexible with regard to what you do within your WorkSheet. By that I mean that you can delete columns and rows, insert columns and rows, move data etc, etc, etc, and you don't have to worry about mucking the Function up because it's the Array Formula that is IMPORTANT because it's the Array Formula that uses the Function.

The formulas system you created is so close to be accurate, did you look at what was the problem and get the right return?

No, and to be honest I will have another look at it in the future when I have got time to fully re-think the problem with the parameters. It is not as straight forward as you think.

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[serge]

Hi PAB,

So if I understood you right, I can move , delete, add, etc... anywhere on the file, as long as I put those 5 Array formulas it will return the combinations needed.

For the formulas system if it too difficult , forget about it.

I did copy and paste the code in my new file and it return the same combination on all the rows ?

Question : Your file name is : Lex2Num
mine is : Combinaisons a Jouer

Does is need to be change is the code ?

Thank you.

Regards,
Serge.

 

[serge]

Hi PAB I 'll be back in 1 hour .

Regards,
Serge.

 

[PAB]

Hi Serge,

So if I understood you right, I can move, delete, add, etc... anywhere on the file, as long as I put those 5 Array Formulas it will return the combinations needed.

Yes, that's correct.

I did copy and paste the code in my new file and it return the same combination on all the rows?

Question : Your file name is : Lex2Num
Mine is : Combinaisons a Jouer

Does is need to be change is the code?

The Lex2Num is NOT a file name, it is the Function name, and has nothing really to do with ANY WorkSheet in ANY WorkBook regardless of file name.

In the file that you have copied the Function into, if the WorkSheet setup is different from the WorkSheet setup that I uploaded for you, then you will ONLY need to change the cell reference in the Array Formula to that of where the Lexicographic number is stored, that's it.

Are you sure that the Formulas are Array Formulas, you can see that these are correct because Array Formulas entered correctly will have Curly Brackets at each end of the Formula???

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[serge]

Hi PAB,

Sorry but it took longer than what I thought !

I upload a fresh file to try it, could you tell me what I did wrong because I get the same number in every cells !!!

https://app.boxcn.net/s/r71cgnz3cxmizh7p6w5p

Thank you.

Regards,
Serge.

 

[PAB]

Hi Serge,

I upload a fresh file to try it, could you tell me what I did wrong because I get the same number in every cells!!!

I know what it is that you haven't done in order for it to work.
You need to highlight the five cells that the combination is going to be produced in and enter the Array Formula, and that's it.
You can't enter the Array Formula into a single cell and copy it across because it doesn't work like that.
An Array Formula is based on a collection of cells as opposed to a single cell.

Please let me know if it is OK.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[PAB]

Hi Serge,

For example, highlight the cells K8:O8, enter the Formula =Lex_2_Num(I8) and then press Ctrl-Shift-Enter, that's it.

I hope this helps!

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[serge]

Hi PAB,

Yes, that was my mistake, now it works fine.

Thank you very much for all your precious help, without you I wouldn't make it, I also know a little bit more about macro.

Thank you.

Regards,
Serge.

 

[PAB]

You're welcome Serge , if you have any further problems then please let me know.

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[serge]

Hi PAB,

Yes I will, Thank you again, I really do appreciate your help.

Regards,
Serge.

 

[serge]

Hi PAB,

I have NO further problems, the last code you gave me work perfect for every games I'm playing.

I just want to Thank you for all the time and effort you spent helping me in this matter.

Regards,
Serge.

 

[PAB]

Hi Serge,

I have NO further problems, the last code you gave me work perfect for every games I'm playing.

I just want to Thank you for all the time and effort you spent helping me in this matter.

You are most welcome, I am glad we got there in the end !

Regards,
PAB


-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-∏-
12:45, restate my assumptions.
(1) Mathematics is the language of nature.
(2) Everything around us can be represented and understood through numbers.
(3) If you graph the numbers of any system, patterns emerge. Therefore, there are patterns, everywhere in nature.

 

[serge]

Hi PAB,

Long time since I've been in this site but I'm coming because I got a problem with the table.
when I set it up for the game 5/39 and I put the Lexi number 325371 I get the combination wrong which is : 6 - 14 - 25 - 31 - 31
The 31 is a repeat ???? could you please look at it and I'm sure you could find the error somewhere.

Thank you in advance for your help.
Here is the link for the Excel file.

https://app.boxcn.net/s/tq00hhdlvtjo1bo463tgrsqa5h0kdht6

Serge.

 

[serge]

Sorry about this post