Need help with a spreadsheet, looking for formula that searches for numbers

Topic

New Reply

Hello, I have a problem, I’m 63 and I can’t see through it anymore.

I have an ods spreadsheet, which calculates the past days, hours, minutes and seconds, etc. from a certain date to TODAY and TOMORROW.

I would like a formula in cell Y that checks whether cell E-U contains a number that has at least a 4-digit sequence (i.e. 0123, or 6789, or any sequence of 4 consecutive numbers), or if there is a 666 or 888 can be formed from the numbers in a line E-U. If so, cell Z should say “YES”.

As a proactive example, on February 12, 2023, the seconds U4 result in 1297615800, from which a 56789012 sequence can be formed. Since there are at least 4 in a row, there should be a “YES” in Z4.

Which one of you is smart enough to solve the problem, unfortunately I’m not? Thanks

P.S.: MS Office Sheet shows some errors but these numbers are not important:

Reply | Quote

Replies

This is not a finished solution but should give you enough to get started.

The following is a user defined function (UDF) that will work on a single cell, you’ll need to add the code to loop through the columns.

*** NOTE: Coded in Excel VBA ***

Option Explicit

Public Function FindSequence(lRow As Long) As String

  Dim zNumStr      As String
  Dim iNumCnt(10)  As Integer
  Dim iCntr        As Integer
  Dim iSeq         As Integer
  Dim iIndex       As Integer
  
    For iCntr = 0 To 9
       iNumCnt(iCntr) = 0
    Next iCntr
    
'*** Convert Cell Value to String
  zNumStr = CStr(Cells(lRow, "E"))
  
'*** Loop through string using value to index into accumulation array
  For iCntr = 0 To Len(zNumStr) - 1
     iIndex = CInt(Mid(zNumStr, iCntr + 1, 1))
     iNumCnt(iIndex) = iNumCnt(iIndex) + 1
  Next iCntr
  
  iSeq = 0
  
 '*** Loop Through accumulation array and keep count
 '*** resetting when 0 found!
 '*** Note: this does not account for wrap arounds 8901!
 
  For iCntr = 0 To 9

'*** Modified per Syk's suggestion to stop once 4 in seq found ***     
     If (iNumCnt(iCntr) > 0) Then
       iSeq = iSeq + 1
     Else
       iSeq = 0
     End If
     
    If (iSeq > 3) Then Exit For
    
  Next iCntr
  
  If (iSeq > 3) Then
    FindSequence = "YES"
  Else
    FindSequence = "NO"
  End If
  
End Function 'FindSequence

Here’s a shot of the test worksheet where you can see the calling sequence and results of the test items:

HTH

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

2 users thanked author for this post.

Wadenbeisser, Sky

Reply | Quote

Wouldn’t the “If (iNumCnt(iCntr) > 0)” conditional have incorrect behaviour if the cell doesn’t contain a 9 (or if it doesn’t contain any particular number after a sequence has already been found)? The cell could contain a > 3 sequence but iSeq would be reset to 0 upon finding no 9. I think that you need to break out the loop when iSeq > 3 to fix this (you could combine the last conditional in the function with this if you wanted to be efficient).

As for accounting for wraparounds, a conditional that checks if iSeq > 0 when iCntr = 9 could do this, perhaps with recursion.

1 user thanked author for this post.

RetiredGeek

Reply | Quote

Sky,

Excellent suggestion! See code above for correction to exit loop once sequence of 4 is found.

Not sure about the recursion thing for wraparound. I’m sure it can be done but it’s going to take a bit of code to accomplish it.

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

1 user thanked author for this post.

Sky

Reply | Quote

Just a note on your correction: you’ve got “For iCntr = 0 To 9” twice.

I’ll be honest, VBA isn’t a programming language I know, so I can’t write what I mean about the recursion thing in the language, but here’s what I mean in Python, as that’s a fairly widely known language which seems to look similar. This replaces the “For iCntr = 0 To 9” loop:

def recursionFunction(iNumCnt, iCntr, iSeq, wraparound):
    if iNumCnt[iCntr] > 0:
        iSeq = iSeq + 1
    else:
        iSeq = 0

    if iSeq < 4:
        if iCntr == 9:
            if iSeq > 0:
                iSeq = recursionFunction(iNumCnt, 0, iSeq, True)
        elif wraparound == False or (wraparound == True and iSeq > 0):
            iSeq = recursionFunction(iNumCnt, iCntr + 1, iSeq, wraparound)

    return iSeq

iSeq = recursionFunction(iNumCnt, 0, 0, False)

Perhaps you can convert this to VBA. Since we’re using some recursion, I’ve changed it to be fully recursive – I hope that you don’t mind.

1 user thanked author for this post.

RetiredGeek

Reply | Quote

As much as I enjoy the functional nature of recursion, a much simpler solution for the wraparound occurred to me this morning, which only modifies your original code very slightly, RetiredGeek. Hopefully I’ve made the very minor changes correctly. I’ve just replaced “iCntr = 0 To 9” with “iCntr = 0 To 12” and “iNumCnt(iCntr)” with “iNumCnt(iCntr Mod 10)”:

For iCntr = 0 To 12
 
   If (iNumCnt(iCntr Mod 10) > 0) Then
     iSeq = iSeq + 1
   Else
     iSeq = 0
   End If
     
   If (iSeq > 3) Then Exit For
    
Next iCntr

Rather tidy, don’t you think, RetiredGeek? If you’re anything like me you’ll appreciate how simple the solution is.

Reply | Quote

Sky,

That’s a great piece of logic! Updated code below:

Option Explicit

Public Function FindSequence(lRow As Long) As String

  Dim zNumStr      As String
  Dim iNumCnt(10)  As Integer
  Dim iCntr        As Integer
  Dim iSeq         As Integer
  Dim iIndex       As Integer
  
    For iCntr = 0 To 9
       iNumCnt(iCntr) = 0
    Next iCntr
    
'*** Convert Cell Value to String
  zNumStr = CStr(Cells(lRow, "E"))
  
'*** Loop through string using value to index into accumulation array
  For iCntr = 0 To Len(zNumStr) - 1
     iIndex = CInt(Mid(zNumStr, iCntr + 1, 1))
     iNumCnt(iIndex) = iNumCnt(iIndex) + 1
  Next iCntr
  
  iSeq = 0
  
 '*** Loop Through accumulation array and keep count
 '*** resetting when 0 found!
 '*** Note: Now accounts for wrap arround 8901!
 '***       Thanks to Sky for the exellent idea!
 
  For iCntr = 0 To 12
  
     If (iNumCnt(iCntr Mod 10) > 0) Then
       iSeq = iSeq + 1
     Else
       iSeq = 0
     End If
     
    If (iSeq > 3) Then Exit For
    
  Next iCntr
  
  If (iSeq > 3) Then
    FindSequence = "YES"
  Else
    FindSequence = "NO"
  End If
  
End Function 'FindSequence

Test Data:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

2 users thanked author for this post.

Wadenbeisser, Sky

Reply | Quote

Can I please have your work file? I have Office 2019 installed but cant integrate your vba script so it would work.

Thanks in advance

Reply | Quote

Thanks for your help, hopefully I will find a way to integrate that into open office

Reply | Quote

Is there an easy way to renumber the contents of a cell with ascending numbers, for example when the content shows 196749237 => 12346779 ?

This would make things easier for me too, I have about 600 events to go trough each day

Reply | Quote

I won’t attempt the VBA, but, since you already know how to get iNumCnt, all you need to do is have an outer loop iterating from 0 to 9 inclusive, an inner loop iterating from 0 to iNumCnt(iCntr) exclusive, and within each inner loop add iCntr to the end of the return string.

In Python:

string = "";

for iCntr in range(0, 10):
    for x in range(0, iNumCnt[iCntr]):
        string = string + str(iCntr)

Just make sure to keep an eye on the inclusive/exclusive nature of the limits of the loops, as in Python they are exclusive and in VBA, from RetiredGeek’s code, they look to be inclusive.

Reply | Quote