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:
 

time*treat

Member
It has similarities to Repeat Marker/Slayer, but this one isn't my work.
(I thought it more matched Frank's style.)
 

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.
 

Frank

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

I did find a bug in that program, it failed to recognise the 7th balls in any result. Corrected in the version I sent direct to Bloubul. For those who helped themselves to it before the link was deleted, tough luck, you don’t get support when you don’t converse.
 

bloubul

Member
Frank / time*treat
Thanks for your response, than I will just have to stick with pairs, what I do know of PAB way of writing that there are always reference to him by his name PAB in any macro.

This is part of my database. It can be in any columns.
1 10/03/2019 1 4 7 11 18
2 11/03/2019 7 10 19 24 32
3 12/03/2019 11 16 17 29 32
4 13/03/2019 4 10 16 21 35
5 14/03/2019 1 5 9 26 32
6 15/03/2019 9 10 12 25 27
7 16/03/2019 2 5 16 18 19
8 17/03/2019 5 9 15 21 36
9 18/03/2019 1 18 29 33 36
10 19/03/2019 7 9 11 19 26
11 20/03/2019 2 16 19 29 33
12 21/03/2019 2 7 10 14 23
13 22/03/2019 9 12 17 33 35
14 23/03/2019 3 7 10 13 17
15 24/03/2019 1 21 24 28 29
16 25/03/2019 10 11 12 19 23
17 26/03/2019 2 9 13 30 32
18 27/03/2019 4 5 10 12 25
19 28/03/2019 7 20 30 31 34
20 29/03/2019 1 5 6 27 31
21 30/03/2019 16 17 24 25 30
22 31/03/2019 1 15 23 32 34
I want to add this formula to another 3 columns next to each draw.

Formula
1.First Nr + Last Nr ÷ By Draw Day = Sum
2.First Sum + Last Nr = Sum
3.First Nr + New Draw Day = Sum
4.Must be 3 digits only, if smaller than 3 digits a "0" must be
added in front.

Example: ( I have just use random numbers)
1.) First Nr + Last Nr ÷ By Draw Day = Sum
01 +22 ÷ 23(Day) = 096 (Code) (0+9+6=15)

2.) First Sum + Last Nr = Sum
0,96 + 22 = 118 (Code) (1+1+8=10)

First Nr + New Draw Day = Sum
01 + 24(Day) = 025 (Code) (0+2+5=7)
What formula(s) can I use to accomplish this calculation(s)

BlouBul :cool:
 

time*treat

Member
... (snipped) ...
I want to add this formula to another 3 columns next to each draw.

Formula
1.First Nr + Last Nr ÷ By Draw Day = Sum
2.First Sum + Last Nr = Sum
3.First Nr + New Draw Day = Sum
4.Must be 3 digits only, if smaller than 3 digits a "0" must be
added in front.

Example: ( I have just use random numbers)
1.) First Nr + Last Nr ÷ By Draw Day = Sum
01 +22 ÷ 23(Day) = 096 (Code) (0+9+6=15)

2.) First Sum + Last Nr = Sum
0,96 + 22 = 118 (Code) (1+1+8=10)

First Nr + New Draw Day = Sum
01 + 24(Day) = 025 (Code) (0+2+5=7)
What formula(s) can I use to accomplish this calculation(s)

BlouBul :cool:
How does 1 + 22 / 23 = 96, in the first formula?
 

bloubul

Member
Hi time*treat
Sorry for the tipo it must be 1+22/24 =0.958, round up to 096.
I cannot login on my pc as before so I had to use my cell phone so unfortunately
an error slipped in.

BlouBul :cool:
 

Frank

Member
Well I cant help. I cant figure out what you are doing.

You give us a list of actual data, then give examples using random numbers not in your data. How is that going to explain what you are doing?

You mention Draw day. What is a draw day? day of week e.g. Sunday =1 Monday=2,,,? draw number? something else?

You mention New draw day. What is that and where is it ?

You talk about code which you evaluate by adding digits of previous sums. But what do you do with this code? You asked for 3 extra columns, but they are already filled with the first 3 sums. Where do you put this code ??
You need to define your terms to be more clear, use ACTUAL figures from the data in your examples and clarify exactly what you want to display in each of the THREE columns.
Thanks
 
Last edited:

bloubul

Member
Frank / time*treat
Thank you for your response, I'm putting this posting for the time being on ice, we have re-open our BB Lodge for business again but we are restricted to 30% of our staff meaning that me and the other manager has to work much longer hours, thus leaves me with no time at present to follow this matter any further for now.

BlouBul :cool:
 

Sidebar

Top