How to count "consecutive cells pattern" occurrence within a range

Sinuhet

New Member
Hi
I have a a single column with range of A1:A100 with cells containing just a single number: -1, 0 or 1.
I would like to count occurrence of cell patterns.
For example cell D16 should have count how many times the -1,1,-1 occurred in 3 adjacent sells within the range A1:A100.
I tried with COUNTIF(S) with no luck.

EURUSD random SIM.xlsx
ABCDEFGHIJKLMN
1-1Sequence combination
2-1Position345678910111213
3-11-1-1-1-1-1-1-1-1-1-1-1
4-1211111111111
513-11111111111
6-14-1111111111
7-15-111111111
8-16-11111111
9-17-1111111
1018-111111
1119-11111
12110-1111
13111-111
14112-11
15113-1
161Count:???????????
171
181
190
201
211
221
231
241
25-1
26-1
27-1
28-1
291
301
311
321
331
341
351
361
37-1
38-1
391
401
411
421
431
441
451
461
471
48-1
49-1
50-1
51-1
52-1
531
54-1
55-1
56-1
57-1
58-1
59-1
601
611
62-1
63-1
64-1
65-1
66-1
671
681
691
70-1
71-1
72-1
73-1
74-1
75-1
76-1
77-1
78-1
79-1
801
811
821
831
841
851
861
871
881
891
901
911
921
931
94-1
95-1
961
971
981
991
1001
Sheet1
Cells with Conditional Formatting
CellConditionCell FormatStop If True
A1:B103Cell Value<0textNO
D3:N15,P3:P252Cell Value>0textNO
D3:N15,P3:P252Cell Value<0textNO

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

lrobbo314

Well-known Member

SEQ Pattern.xlsm
ABCD
1-1CombinationCount
2-132
3-141
4-151
51101
6-1111
7-1161
8-1
9-1
101
111
121
131
141
151
161
171
181
190
201
211
221
231
241
25-1
26-1
27-1
28-1
291
301
311
321
331
341
351
361
37-1
38-1
391
401
411
421
431
441
451
461
471
48-1
49-1
50-1
51-1
52-1
531
54-1
55-1
56-1
57-1
58-1
59-1
601
611
62-1
63-1
64-1
65-1
66-1
671
681
691
70-1
71-1
72-1
73-1
74-1
75-1
76-1
77-1
78-1
79-1
801
811
821
831
841
851
861
871
881
891
901
911
921
931
94-1
95-1
961
971
981
991
1001
Sheet2

VBA Code:
``````Sub SEQ()
Dim AR() As Variant:        AR = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row).Value2
Dim b As Boolean:           b = False
Dim cnt As Long:            cnt = 0
Dim SD As Object:           Set SD = CreateObject("Scripting.Dictionary")
Dim r As Range

For i = 1 To UBound(AR)
Select Case AR(i, 1)
Case -1
If cnt > 0 Then SD(cnt + 2) = SD(cnt + 2) + 1
b = True
cnt = 0
Case 0
b = False
cnt = 0
Case 1
If b Then cnt = cnt + 1
End Select
Next i

Range("C1:D1") = Array("Combination", "Count")
Set r = Range("C2").Resize(SD.Count, 1)
r.Value2 = Application.Transpose(SD.keys())
r.Offset(, 1) = Application.Transpose(SD.items())
Set r = r.Resize(, 2)
r.Sort r.Cells(1, 1), xlAscending
End Sub``````

Sinuhet

New Member
Thank you. I was hoping there is a way to avoid VBA (as I am not very experienced) and use a combination of excel build in functions. This of course might not be possible and the VBA is then the only solution. I will try your VBA tomorrow. Thank you

Eric W

MrExcel MVP
One way with formulas:

Dynamic functions.xlsm
ABCDEFGHIJKLMN
1-1Sequence combination
2-1Position345678910111213
3-11-1-1-1-1-1-1-1-1-1-1-1
4-1211111111111
513-11111111111
6-14-1111111111
7-15-111111111
8-16-11111111
9-17-1111111
1018-111111
1119-11111
12110-1111
13111-111
14112-11
15113-1
161Count:211????????
171
Sheet4
Cell Formulas
RangeFormula
D16D16=SUMPRODUCT(--((A1:A98=D3)+(A2:A99=D4)+(A3:A100=D5)=3))
E16E16=SUMPRODUCT(--((A1:A97=E3)+(A2:A98=E4)+(A3:A99=E5)+(A4:A100=E6)=4))
F16F16=SUMPRODUCT(--((A1:A96=F3)+(A2:A97=F4)+(A3:A98=F5)+(A4:A99=F6)+(A5:A100=F7)=5))

You can see the pattern in creating the formulas. You'd just have to continue the pattern through N16.

The new Excel 365 calculation engine has some new features that would allow a formula that you could put in D16 and just drag to the right. Unfortunately I don't have that yet so I can't offer that. I noticed that each of your patterns starts with a -1, has all 1's, then ends with a -1. If that is always the case, I could probably come up with a (complicated) single formula to handle that.

lrobbo314

Well-known Member

SEQ Pattern.xlsm
ABCDEFGHIJKLMNOPQR
1-1Sequence combination
2-1Position34567891011121314151617
3-11-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
4-12111111111111111
513-111111111111111
6-14-11111111111111
7-15-1111111111111
8-16-111111111111
9-17-11111111111
1018-1111111111
1119-111111111
12110-11111111
13111-1111111
14112-111111
15113-11111
16114-1111
17115-111
18116-11
19017-1
201Count:211000011000010
211
221
231
241
25-1
26-1
27-1
28-1
291
301
311
321
331
341
351
361
37-1
38-1
391
401
411
421
431
441
451
461
471
48-1
49-1
50-1
51-1
52-1
531
54-1
55-1
56-1
57-1
58-1
59-1
601
611
62-1
63-1
64-1
65-1
66-1
671
681
691
70-1
71-1
72-1
73-1
74-1
75-1
76-1
77-1
78-1
79-1
801
811
821
831
841
851
861
871
881
891
901
911
921
931
94-1
95-1
961
971
981
991
1001
Sheet3
Cell Formulas
RangeFormula
D20:R20D20=LET(lst,TEXTJOIN("",TRUE,\$A\$1:\$A\$100),pat,TEXTJOIN("",TRUE,D3:INDEX(D3:D19,COUNTA(D3:D19))),sub,SUBSTITUTE(lst,pat,"@"),ar,MID(sub,ROW(INDIRECT("1:"&LEN(sub))),1),tot,SUMPRODUCT(--(ar="@")),tot)

Eric W

MrExcel MVP
I blatantly stole lrobbo's clever idea, but here's a version that works in Excel 2019 and up:

Dynamic functions.xlsm
ABCDEFGHIJKLMN
1-1Sequence combination
2-1Position345678910111213
3-11-1-1-1-1-1-1-1-1-1-1-1
4-1211111111111
513-11111111111
6-14-1111111111
7-15-111111111
8-16-11111111
9-17-1111111
1018-111111
1119-11111
12110-1111
13111-111
14112-11
15113-1
161Count:211????????
17121100001100
181
Sheet4
Cell Formulas
RangeFormula
D16D16=SUMPRODUCT(--((A1:A98=D3)+(A2:A99=D4)+(A3:A100=D5)=3))
E16E16=SUMPRODUCT(--((A1:A97=E3)+(A2:A98=E4)+(A3:A99=E5)+(A4:A100=E6)=4))
F16F16=SUMPRODUCT(--((A1:A96=F3)+(A2:A97=F4)+(A3:A98=F5)+(A4:A99=F6)+(A5:A100=F7)=5))
D17:N17D17=SUMPRODUCT(--(MID(CONCAT(IF(\$A\$1:\$A\$100=-1,"x",\$A\$1:\$A\$100)),ROW(INDIRECT("1:"&(101-COUNTA(D\$3:D\$15)))),COUNTA(D\$3:D\$15))=CONCAT(IF(D\$3:D\$15=-1,"x",D\$3:D\$15&""))))
Press CTRL+SHIFT+ENTER to enter array formulas.

With Excel 365, I would suggest using SEQUENCE instead of the ROW(INDIRECT( structure.

lrobbo314

Well-known Member

Here's a better version that doesn't rely on row(indirect()).

SEQ Pattern.xlsm
CDEFGHIJKLMNOPQR
1Sequence combination
2Position34567891011121314151617
31-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
42111111111111111
53-111111111111111
64-11111111111111
75-1111111111111
86-111111111111
97-11111111111
108-1111111111
119-111111111
1210-11111111
1311-1111111
1412-111111
1513-11111
1614-1111
1715-111
1816-11
1917-1
20Count:211000011000010
Sheet3
Cell Formulas
RangeFormula
D20:R20D20=LET(lst,TEXTJOIN("",TRUE,\$A\$1:\$A\$100),pat,TEXTJOIN("",TRUE,D3:INDEX(D3:D19,COUNTA(D3:D19))),sub,SUBSTITUTE(lst,pat,"@"),dif,LEN(lst)-LEN(sub),dif/(LEN(pat)-1))

lrobbo314

Well-known Member
Very cool solution Eric! Even though the idea is similar, I never would have thought to do it like that. That's why I love this forum.

I totally forgot about sequence too. Force of habit with mid(row(indirect...

lrobbo314

Well-known Member
Just for kicks, a way using Power Query as well.

SEQ Pattern.xlsm
DEFGHIJKLMNOPQR
134567891011121314151617
2-1-1-1-1-1-1-1-1-1-1-1-1-1-1-1
3111111111111111
4-111111111111111
5-11111111111111
6-1111111111111
7-111111111111
8-11111111111
9-1111111111
10-111111111
11-11111111
12-1111111
13-111111
14-11111
15-1111
16-111
17-11
18-1
19211000011000010
Sheet1

Power Query:
``````let
Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
Transpose = Table.Transpose(Demote),
toList = Table.AddColumn(Transpose, "sList", each Text.Combine(List.Transform(List.Skip(List.RemoveItems(Record.FieldValues(_),{null}),1),Text.From),"")),
Full = Table.AddColumn(toList, "fList", each Text.Combine(List.Transform(Table1[Column1],Text.From),"")),
sLen = Table.AddColumn(Full, "sLen", each Text.Length([sList])-1),
Replace = Table.AddColumn(sLen, "Custom", each Text.Replace([fList],[sList],"@")),
Total = Table.AddColumn(Replace, "Custom.1", each (Text.Length([fList])-Text.Length([Custom]))/[sLen]),
RC = Table.RemoveColumns(Total,{"fList", "sLen", "Custom", "sList"}),
TransposeBack = Table.Transpose(RC),
in
Promote``````

Sinuhet

New Member
Gentlemen, thank you both for your help. I can now define a pattern in financial market time series and count their frequency. I know excel might not be the best tool for it, but it is sufficient for some basic screening.

Replies
10
Views
147
Replies
5
Views
52
Replies
1
Views
79
Replies
6
Views
255
Replies
2
Views
80

1,141,125
Messages
5,704,440
Members
421,349
Latest member
Santhosh3188

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back