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

Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » Need help with a spreadsheet, looking for formula that searches for numbers

Author
Topic
#2533445

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:

Author
Replies
• #2533495

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
• #2533649

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
• #2533700

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
• #2533751

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
• #2533885

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.

• #2533971

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
```

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

###### 2 users thanked author for this post. Wadenbeisser, Sky
• #2535065

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

• #2533794

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

• #2533795

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

• #2533888

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.