Formula

bloubul

Member
Good Morning.
I'm looking for a excel formula to find all the nrs who follows a number in a range.

Please stay safe.

BlouBul :cool:
 

Frank

Member
Bloubul,

It’s not clear exactly what you want, what does ‘follow ‘ mean, what do you mean by ‘range’ is that a cell or range of cells , what are the constraints in the numbers. You need to show an example of the input and an example of the desired output to make this clearer. If this does require a formula it may be an array formula if multiple outputs are required, failing that a macro may be required. I’m not as good as I’d like to be with array formulas , but we will see what can be done if you clarify your question.
 

Frank

Member
I'm sorry but that spreadsheet is not helping. I have not got the slightest idea what you are doing. You have this table on the right columns N to BK with numbers in them. The numbers mirror across the diagonal which you have blacked out. You do not appear to be interested in when a number follows itself.
What are these numbers? What process takes place to arrive at these numbers?
Example:- cell O2 contains number 7. 7 what? Number 2 does not follow number 1 7 times, so what is going on? Why 7 ?

I cant help you if you don't tell me what you are doing and why your data is not in sorted order.
 

bloubul

Member
Frank
The count given in columns N to BK are fictitious, I want to find out how many times the remaining 35 numbers follow no 1 for example, than no 2.....eg...eg. I have tried to use the =ifcount function but it does not work out correctly.

I only use the drawn order for distribution purposes

BlouBul :cool:
 

Frank

Member
Well it’s a bit confusing because your data has a highest ball number of 45. Yet your table is a 50 ball by 50 ball table. Also you talk about ball 1 and ‘ the remaining 35’, suggesting it’s a 36 ball lottery. None of it is adding up. Just how many balls are in this lottery?
Also I’m getting a feeling of deja vu here. In 2015 there was a topic about Markov chains and I gave a tutorial on creating a ‘ follow on table’ macro driven spreadsheet for a 49 ball lottery. https://lottoforums.com/threads/markov-chains.12420/
This specifically answered the question “ How many of each ball appeared in the NEXT draw over lottery history for every ball? it created a table exactly like the one in your example, except the diagonal was not blacked out because it also included when a ball followed ITSELF in the next draw, i.e a direct repeat. Fundamentally it relied on many macros. To do this with a formula, keeping track of each ball in each draw and adding the 6 drawn balls In the NEXT draw to a unique counter for each ball , would be difficult if not impossible. So I’m suggesting a formula to do all this is not possible.
Is your request covering the same territory As 2015? Is that the question you are asking?
As I recall , you were one of the few people who attempted to make that sheet.
Did you succeed? Have you tried it For a 35 ball lottery? It can be modified if you get an error message!
 

Frank

Member
The 2015 spreadsheet had the data with newest draw at the top. Yours is newest at the bottom, so that would need to be borne in mind.
 

Frank

Member
Actually, there may be a workaround to use formulas to do a table for one ball and its followers, then change the ball and it updates for the followers of the new ball.... watch this space..
 

Frank

Member
Ok, this should work for any lottery which has up to 7 drawn balls, has no more than 59 balls and has data with the oldest draw at he top, newest (highest draw number) at the bottom. It looks for balls drawn in the following draw for any and all balls.
It does use one small macro to asemble a table of all the results, but you dont need to run it if happy to check one ball at a time. it takes about half a minute to run with current data, but may take more for bigger lotteries and more results. I havent found any bugs, let me know if you find any.

https://www.mediafire.com/file/63amz6qbcxo8riz/Numbers_Follow_Numbers2.xlsm/file
 

Frank

Member
Due to no reply from Bloubul and 11 downloads from freeloaders without comment, the above link is deleted.
 

Frank

Member
I’ve had an email from Bloubul, he is having difficulty accessing the site. He awaits a response from LT.
 

bloubul

Member
Hi All
Thank you Frank for the suggestion yo use my cell phone. after a few trails I got it right.

time*treat
I think you wrote this macro, how can I change it to: myNum = InputBox("seaRch foR...", "select a paiR of numbeRs like 16-36", "16-21-36","16-21-28-36","16-21-28-36-41","16-21-28-36-41-50")
This the macro currently:
Sub SeaRchFoRAPaiR_02()
'Oct 12, 2014
Dim myNum As String
myNum = InputBox("seaRch foR...", "select a paiR of numbeRs like 16-36", "16-36")
If myNum = "" Then Exit Sub
Dim v As Variant
v = Split(myNum, "-")
Const FiRstR As Long = 3 '<< souRce data, fiRst Row
Const FiRstC As String = "A" '<< fiRst column
Const LastC As String = "G" '<< last column
Const TaRgetC As String = "Q" '<<< fiRst TaRget Column
Dim ws As Worksheet
Set ws = ActiveSheet
Dim R As Long, c As Long, i As Long, t As Long, N As Long
R = ws.Cells(Rows.Count, FiRstC).End(xlUp).Row
N = ws.UsedRange.Rows.Count
c = ws.Range(ws.Cells(FiRstR, FiRstC), ws.Cells(FiRstR, LastC)).Columns.Count
ws.Range(FiRstC & ":" & LastC).Interior.ColorIndex = xlNone
ws.Cells(1, TaRgetC).Resize(N, c + 1).Clear
If UBound(v) <> 2 Then MsgBox "wRong, select 3 numbeRs": Exit Sub
Application.ScreenUpdating = False
Dim R1 As Range, R2 As Range
t = 2
For i = FiRstR To R
Set R1 = ws.Cells(i, FiRstC).Resize(, c).Find(What:=v(0), LookIn:=xlValues, LookAt:=xlWhole)
Set R2 = ws.Cells(i, FiRstC).Resize(, c).Find(What:=v(1), LookIn:=xlValues, LookAt:=xlWhole)
If Not R1 Is Nothing And Not R2 Is Nothing Then
R1.Interior.Color = RGB(255, 255, 0): R2.Interior.Color = RGB(255, 255, 0)
ws.Range(ws.Cells(i - 1, FiRstC), ws.Cells(i, LastC)).Copy ws.Cells(t, TaRgetC)
ws.Cells(t, TaRgetC).Offset(, c) = "Row " & i - 1: ws.Cells(t + 1, TaRgetC).Offset(, c) = "Row " & i
t = t + 2
End If
Next
R = ws.Cells(Rows.Count, TaRgetC).End(xlUp).Row
If R = 1 Then MsgBox "nothing found": Exit Sub
ws.Range(ws.Cells(1, FiRstC), ws.Cells(1, LastC)).Copy ws.Cells(1, TaRgetC)
ws.Cells(1, TaRgetC).Resize(R, c + 1).EntireColumn.AutoFit
ws.Range(FiRstC & ":" & LastC).Interior.ColorIndex = xlNone
Application.ScreenUpdating = True
End Sub

BlouBul :cool:
 

Frank

Member
Im pretty sure I didn’t write this one. I’ve never used the VBA split function. Most likely PAB wrote it . It’s from his era and it’s his style to date his work . You know me, I never write a macro to do a job that can be done using formulas, that’s probably what I would have done.
 
Top