0

How can I find multiple instances of presence of consecutive numbers in a column of big data of 1's and 0's in excel spreadsheet. For example, my excel column is given below:

0                    
0  
1   
1   
0  
0  
1  
0  
1   
0  
0  
1  
1   
0  
1  
1  
1  
0  
0  
1   
1    
0    
0  
1   
0

Please assume this as some of my column data, I need to find wherever the sequences of 1's and 0's present in the column.

I defined a function for finding single instance as below:

Function FINDSEQ(seq As String, rng as Range) As Long
    FINDSEQ = InStr(1, Join(Application.Transpose(rng.Value), ""), seq)
End Function

But I couldn't find how to find multiple instances if present. For Example, I need to find:

1    
1  
0   
0   
1  
0

as the sequence of consecutive numbers, what change should I do?
Here 1 1 0 0 1 0 is present two times, but based on the above defined function I could only find a single instance.

JvdV
  • 70,606
  • 8
  • 39
  • 70
Aashiq Shajahan
  • 55
  • 5

3 Answers3

2

Try the next function, please:

Function FINDSEQ(seq As String, rng As Range) As Long
 FINDSEQ = UBound(Split(Join(Application.Transpose(rng.Value), ""), seq))
End Function

And call it in the next way:

Sub testFINDSEQ()
  Dim sh As Worksheet, rng As Range
  
  Set sh = ActiveSheet
  Set rng = sh.Range("A1:A25")
  Debug.Print FINDSEQ("110010", rng)
End Sub

Or from a cell (like UDF):

=FINDSEQ("110010",A1:A25)

Do not forget to delete your already existing function

FaneDuru
  • 38,298
  • 4
  • 19
  • 27
  • 2
    Upvoted, OP is not clear if he wants to know instances or positions. I think positions because of the original UDF. Be aware that you are not finding overlapping hits. + – JvdV Jul 27 '20 at 08:51
  • 1
    @JvdV: He's not too clear, indeed. I asked a long clarification comment, but I deduced that he was unsatisfied with his UDF function result... He wanted more occurrences. That's why I deleted my comment supposing that my last understanding would be correct. In such a case, I think that the overlapping will not count . Now, only he will be able to clarify the issue... :) – FaneDuru Jul 27 '20 at 09:00
2

Add a sp (starting position) parameter to the FINDSEQ function:

Function FINDSEQ(seq As String, rng As Range, sp As Integer) As Long
    FINDSEQ = InStr(sp, Join(Application.Transpose(rng.Value), ""), seq)
End Function

and then continue the search from the immediate next position from any found matching string:

newpos=FINDSEQ(1)

Do While newpos>0
    Debug.Print newpos
    newpos=FINDSEQ(newpos+1)
Loop
JMP
  • 4,417
  • 17
  • 30
  • 41
1

Was hoping you would give it a bit more of a try yourself first after your previous question. I'd also go with the first parameter of InStr, but a little different to @JMP:

Function FINDSEQ(rng As Range, seq As String) As String

Dim x As Long, y As Long: x = 1
Do While InStr(x, Join(Application.Transpose(rng.Value), ""), seq) > 0
    y = InStr(x, Join(Application.Transpose(rng.Value), ""), seq)
    If FINDSEQ = "" Then
        FINDSEQ = y
    Else
        FINDSEQ = FINDSEQ & "," & y
    End If
    x = y + 1
Loop

End Function

The function has now also changed from Long type to String.

JvdV
  • 70,606
  • 8
  • 39
  • 70
  • I am really sorry, I am not good at VBA, I just copy pasted this code to VBA and called the function like you answered in my previous question, But it shows #VALUE! error. – Aashiq Shajahan Jul 27 '20 at 08:53
  • 1
    You did `=FINDSEQ(A1:A25,"110010")`? Worked fine for me. Btw, remove the older function first. – JvdV Jul 27 '20 at 08:54
  • Previously I did: =FINDSEQ("110010",A1:A25), it didn't worked. Now It is perfectly fine, Thanks a lot. – Aashiq Shajahan Jul 27 '20 at 08:59
  • 2
    @Aashiq Shajahan: So, you need the overlapping occurrences to be fount, too. In such a case, we hare tick the code left side check box, in order to make it **accepted answer**. In this way, somebody else searching for something similar will know that the code works... – FaneDuru Jul 27 '20 at 09:17