Search of repeating sequences.

[KARAT]

I ask to create table Excel for search of patterns.

We have a database till 1000 lines.

1 2 2 5 1 1 4 2 2 5 2 6 1 2 11 1 3 1 2 6
8 1 2 1 2 2 3 6 1 1 2 3 1 4 1 1 1 13 3 4
2 3 4 4 1 1 2 1 1 4 2 4 2 1 3 1 13 2 5 4
4 4 2 5 3 3 3 3 1 1 3 2 3 1 3 2 8 1 4 4
1 4 3 2 1 2 10 2 3 2 2 3 3 1 1 4 1 3 1 11
Etc.

In each line to 20 numbers it (is established).
Numbers from 1 to 80 it (is established). Numbers in line can repeat.
To find all repeating sequences long from 2 до 20 it (is established), to count quantity.
To allocate with colour the chosen sequence in all database.

For example, to establish length “3”, to calculate.

Result:
Sequence “1 1 4” - 3
Sequence “2 1 2” - 2
Etc.

For example, to establish length “6”, to calculate.

Result:
Sequence "1 2 11 1 3 1"-?
Sequence "2 5 1 2 10 2"-?
Etc.

 

[Frank]

Karat, Iv'e been puzzled by this as I thought immediately that this would be a futile excercise due to the astronomical odds against any repeated sequences longer than a triple.


In the example you posted as illustration you only used low numbers up to 11 but you specified that the numbers can be between 1 and 80. Also the sequences you show contain values that can repeat and therefore appear in any order, not sorted ascending. The odds against even matching a CONSECUTIVE sequence of 2 from 20 (up to 80) in non ascending order are very high such that you will get only a handful in 1000 rows. For a consecutive triple, many will not repeat at all in 1000 draws, and as for anything longer than 3 numbers - forget it - its not going to happen.

That is if I understand your well specified problem correctly.

To prove my thoughts I did a feasibility study on a spreadsheet which created 1000 lines of 20 numbers each between 1 and 80 and repeated numbers allowed on the row.
I then created all the consecutive pairs, triples, quads, quins, sextuplets that these numbers made over the 1000 lines.

I added a counter which would count how many times each named consecutive sequence happenned over 1000 lines.

So you need to enter an existing sequence that you see such as 15,37 into cell AH1 and the counter tells you how many in total there are over 1000 lines. On this sheet the answer is 4. If you enter a triple like 23,15,37 then you get only 1. It doesnt repeat at all!

So it would be an enormous programming task for little return, so I would suggest this is not worth while.

http://www.mediafire.com/file/5crfl2818lecgt7/sequence%20feasiblity.xlsx

 

[jack]

Hello frank, it is possible to increase to 100 numbers
* Ie 00 to 99
* For 20,
Or it's all the same as this is to add from 80 to 100
* Please
My lottery has 100 numbers and 20 are drawn
What you have to do is add to 100
There is! has to locate the positions of the sequences are the positions Do start already enough ,! we have 20 positions or columns Please

 

[jack]

file lotomania
http://www.mediafire.com/file/hdj31i6rce6ulrj/planilia_100_para_sequenciais.xlsx

 

[KARAT]

Frank, you have very well explained. I thank. It agree, calculations can be huge.
And, if numbers in lines are only from 1 to 12 (15)? You can create the table?

jack, in my example in lines not lotto numbers, and delta. I in the beginning have specified too great value from 1 to 80. It is enough For my problem from 1 to 15.

 

[jack]

Ok karak, but the sequences that frank did got a lot of work
* So add up to 100, the rest keep

 

[Frank]

Frank, you have very well explained. I thank. It agree, calculations can be huge.
And, if numbers in lines are only from 1 to 12 (15)? You can create the table?

jack, in my example in lines not lotto numbers, and delta. I in the beginning have specified too great value from 1 to 80. It is enough For my problem from 1 to 15.

KARAT
I am working on modifying the feasibility sheet to restrict the highest number allowed to be 15 and incorporating combination counter macros which find the most prolific sequences up to length 4 and highlight them. I already know there is no point in looking for quins or longer as they are still unique even when limited to a max of 15.

 

[Frank]

This is a development of the earlier sheet, I still have doubts about its usefulness.
The following depends on the actual numbers you have in your table and results therefore vary.
There is a question of what to highlight. You have to draw a line somewhere, there are a few hundred consecutive high scoring pairs, you cant colour code all of them so Ive just identified the top pair(s) and highlight them. The consecutive triples only have high scorers in the range of a count 15 to 20, sometimes a clear leader which I can highlight, sometimes 16 different triples all the same count, again leading to highlighting complications. The same applies to the quads, various ones have a handful of consecutive matches, sometimes all the same low count, the highlighting question can be a problem and the numbers so few as to make me wonder why bother. I have dispensed with quins and sextuplet counting, they are largely unique and just take up memory.
This does use macros to first count the consecutives and then a sort button puts them in order, and the quads macro can take several minutes to run depending on your PC specification.
I hope this is of some use, but any further development would need complicated colour formatting coding which I have neither the time nor inclination to get involved with at this time.

http://www.mediafire.com/file/1r14lxagwuaw4zy/sequence%20finder.xlsm

 

[jack]

Hello Frank for the 100/20 lottery? Please
Link Draws

 

[Frank]

Jack, the sequence feasibility sheet will work without modification for a 100/20 lottery. Pretty pointless at such huge odds though.

The latest sheet "sequence finder" macros will not. To find a triple you would be looking though a possible 1,000,000 sequences , your PC would take forever to check them all if it didn't crash, only to tell you that there are no high frequency repeated sequences.

 

[jack]

Ok you're correct, my goal was to see sequences from a certain position
* In ascending orde

 

[KARAT]

Frank, how you have created 1000 numbers?
I took deltas of 1000 real circulations of lottery KENO, have placed them in the table, have calculated steams, a three and the four. Has received:
The most frequent steams - 2356,
The most frequent three - 690,
The most frequent of the four - 196.

At such quantities the steams allocated with colour, three, the four help to see their behaviour in the history of circulations.
To allocate with colour it is necessary not all found out steams, three, the four, but only chosen and noted.

Small affliction - in mine KENO the maximum delta - 22. Therefore 15 it is not enough.

 

[Frank]

Had you told me that the numbers were DELTAS then I would not have worked with RANDOM numbers in the first place. Why did you not tell me this? This changes everything.
I dont deal with deltas, not my thing.
The frequency distribution of deltas is not level like random numbers, it is seriously skewed in favour of lower deltas like 5 and under, which are the most prolific . Higher numbers like 22 are much rarer. You are now looking at combinations of statistically skewed numbers so you are going to have high counts of the 1,1,1 and 1,2,1s etc for no good reason other than the statistics of probability. Its a decay curve of probabilities for the higher numbers. I dont know why you are bothering!

I will modify the existing sheet to allow higher numbers, but the quads will require more work in the light of your belated revelations. Please remember in future to specify the data source, the exact lottery, its matrix and how the table numbers are arrived at BEFORE asking for help.

 

[jack]

http://www.mediafire.com/file/14u35y6nyndtk6u/tabela_para_sequencias.xlsx

 

[Frank]

Right, I made up a fake set of 80/20 lottery results and then calculated the deltas and used that as data. This meant loads of low number combinations were favoured and any combination with a higher number had a low or zero count.
The need for quad and quin counters was demonstrated and the macros for those had to be completely rewritten to economise on processing time and memory requirements. The top 2 pairs, triples, quads and quins are highlighted, as is any sequence you manually enter on sheet 1. The combinations must be counted and sorted in sequence, particularly the quad and quin counters, e.g the quad counter only works correctly based on sorted results from earlier triple counts, and the quin counter from earlier sorted quad results. There is no in built limitation to the highest number but will only safely work with numbers up to 24.
I have overwritten the earlier version, so the download link is the same.
I have now reached the end of my work here, Ive wasted far too much time already on this.

 

[KARAT]

Frank, excellent work!
Excuse for the inexact description of a problem!
I thank you!

 

[bloubul]

Frank
Again I must tip my hat to you Sir, brilliant work as only you can master, but explain to me how do I transfer deltas 1,2,...1,2,1 and 5,1,1,1,2 into lotto numbers..

BlouBul

 

[Frank]

Well as I said when I discovered this was about Deltas, this is not a topic I know much about. I've never used them and in the UK we have nothing like the Keno where you can have 20 deltas.
The sequences you mention could appear anywhere within a string of 20 and in any order as in quin first, pair second, triple third, or alternatively triple first, pair second etc. Any shortage of digits to make 20 would need to come from other count results as well. Then there's the variable first number on which the whole lotto sequence relies when you add in the deltas ball by ball.

If it were me I'd create a column of starting numbers ( as in the first ball) numbered say 1 to 7 and in the adjacent columns enter strings of deltas based on the above logic. Then further along the spreadsheet 20 columns away, using formulas generate the lotto balls created by the sequences by progressive adding. Who knows one line may end up with a last ball more than 80 which I would reject.
So a combination of change of start ball, change of order of top sequences , change of extra sequence would generate a choice of lotto balls. Then copy the formulas down to subsequent rows.

Thats what I'd do based on common sense, but what do I know? There are others out there with more experience of Deltas than me. KARAT ?

 

[jack]

Hello frank, my keno is 100/20

 

[Frank]

Hello frank, my keno is 100/20

The spreadsheet is designed for DELTAS not the original lotto results. Work out the deltas first on your sorted result set and use them on the sheet. I'd be surprised if any delta exceeds 25.