CODEWITHSUNDEEP
excelvbaexcel-formulaspreadsheetexcel-2016
Aashiq Shajahan
Aashiq Shajahan

Reputation: 55

How to search for a specific data of consecutive numbers in a column of an excel even if multiple instances are present

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.

Upvotes: 0

Views: 344

Answers (3)

FaneDuru
FaneDuru

Reputation: 42236

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

Upvotes: 2

JvdV
JvdV

Reputation: 75900

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.

Upvotes: 1

JMP
JMP

Reputation: 4467

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

Upvotes: 2

Related Questions