index when values repeat then change

[jimblimm]

My setup:

A1:A20

S
S
T
S
S
S
S
U
T
S
S
U
S
S
S
S
t
T
R

I am looking for a formula that will tell me how many times the value "s" streaked
4 times then the value "t" followed. Example

S
S
S
S
T

The answer should be 1

Displayed in c1

And i also need a index of when it occurred in d1

Answer should be 17.

 

[hurgadion]

Hi,
try the code:

Sub SSSST()
Dim tbl(), s As String, i&

tbl = Application.Transpose(Range("a1:a20"))
s = Join(tbl, "")

For i = 1 To Len(s) - 4
  If Mid(s, i, 5) = "SSSST" Then
    x = x + 1
    ReDim Preserve tbl(1 To x)
    tbl(x) = i + 4
  End If
Next i
Cells(1, 3).Value = UBound(tbl)
Cells(1, 4).Resize(UBound(tbl)) = Application.Transpose(tbl)
End Sub

Best regards.

 

[jimblimm]

Is there a regular formula i can use mmult maybe