How to find the most SEQUENTIAL occurring numbers in 1 column

[rebel123]

I am doing lottery odds on excel for fun.
So I am doing a 5 column lottery.

I am trying to figure out in Column I (Labeled 4th Ball)
How to highlight the numbers that show up in a row the most
frequently and to highlight them a certain color.

For example, rows 20-22 all have the number 15 so that
means that 15 occurs 3 times in a row (in these selected row).

I want to know if there are any more that occur 4 times in
a row or 5 times in a row. If not, I would need to know all of the
times it occurs 3 times in a row.


The Excel file is here:http://bit.ly/31nPDLb

 

[Rick Rothstein]

When I wrote this UDF (user defined function), I had misread what you wanted. I thought you wanted to know what the maximum sequence was and which number or numbers had that maximum sequence. This UDF will output the maximum sequence number followed by each ball number that had that maximum sequence in parentheses...

Function MaxSeqColI() As String
  Dim X As Long, Z As Long, Test As String, ColI As Variant, Nums As Variant
  Const MaxBallNumber As Long = [B][COLOR="#FF0000"]60[/COLOR][/B]
  Application.Volatile
  ColI = Range("I1", Cells(Rows.Count, "I").End(xlUp))
  For X = 1 To MaxBallNumber
    Test = Space(UBound(ColI))
    For Z = 1 To UBound(ColI)
      If ColI(Z, 1) = X Then Mid(Test, Z) = 1
    Next
    Nums = Split(Application.Trim(Test))
    For Z = 0 To UBound(Nums)
      If Len(Nums(Z)) > Val(MaxSeqColI) Then
        MaxSeqColI = Len(Nums(Z)) & " (#" & X & ")"
      ElseIf Len(Nums(Z)) = Val(MaxSeqColI) Then
        MaxSeqColI = MaxSeqColI & "(#" & X & ")"
      End If
    Next
  Next
End Function

NOTE 1: I did not know your maximum possible ball number, so I guessed at 60... change it (the red highlighted number) to your actual maximum ball number.

NOTE 2: I'll work on converting this to a macro that highlights those maximum sequences in a color, but until then I thought you might like the above function.

 

[rebel123]

Awesome thanks I will try in the morning when. I'm on my laptop as I'm on my phone now. Thanks !

 

[rebel123]

How do I post the macros?
In what column and row do I put it in?
I own MS Excel for both Mac and I have Google Sheets.
I have MS Excel version 16.24 for Mac.

so does this work for both?

 

[rebel123]

also, how do i sort the numbers in a row in numerical order?
For example the data looks like this and starts at C2 (I re-sorted it),
so how do I get the data to read "1" on L2, "4" on M2, "10" on N2, "18" on N2, and "26" on O2.
All of those numbers are without parentheses of course. Because the correct numerical
order for these is 1-4-10-18-26

C2 D2 E2 F2 G2 H2 I2 J2 K2
<style type="text/css"><!--td {border: 1px solid #ccc;}br {mso-data-placement:same-cell;}--></style>

1

26

18

10

4

<colgroup><col style="width: 64px"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"><col width="64"></colgroup><tbody>
</tbody>