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

[Sinuhet]

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.
Any help please? Thank you

EURUSD random SIM.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	-1			Sequence combination
2	-1		Position	3	4	5	6	7	8	9	10	11	12	13
3	-1		1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
4	-1		2	1	1	1	1	1	1	1	1	1	1	1
5	1		3	-1	1	1	1	1	1	1	1	1	1	1
6	-1		4		-1	1	1	1	1	1	1	1	1	1
7	-1		5			-1	1	1	1	1	1	1	1	1
8	-1		6				-1	1	1	1	1	1	1	1
9	-1		7					-1	1	1	1	1	1	1
10	1		8						-1	1	1	1	1	1
11	1		9							-1	1	1	1	1
12	1		10								-1	1	1	1
13	1		11									-1	1	1
14	1		12										-1	1
15	1		13											-1
16	1		Count:	?	?	?	?	?	?	?	?	?	?	?
17	1
18	1
19	0
20	1
21	1
22	1
23	1
24	1
25	-1
26	-1
27	-1
28	-1
29	1
30	1
31	1
32	1
33	1
34	1
35	1
36	1
37	-1
38	-1
39	1
40	1
41	1
42	1
43	1
44	1
45	1
46	1
47	1
48	-1
49	-1
50	-1
51	-1
52	-1
53	1
54	-1
55	-1
56	-1
57	-1
58	-1
59	-1
60	1
61	1
62	-1
63	-1
64	-1
65	-1
66	-1
67	1
68	1
69	1
70	-1
71	-1
72	-1
73	-1
74	-1
75	-1
76	-1
77	-1
78	-1
79	-1
80	1
81	1
82	1
83	1
84	1
85	1
86	1
87	1
88	1
89	1
90	1
91	1
92	1
93	1
94	-1
95	-1
96	1
97	1
98	1
99	1
100	1
Sheet1

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A1:B103	Cell Value	<0	text	NO
D3:N15,P3:P252	Cell Value	>0	text	NO
D3:N15,P3:P252	Cell Value	<0	text	NO

 

[lrobbo314]

How about this?

SEQ Pattern.xlsm
	A	B	C	D
1	-1		Combination	Count
2	-1		3	2
3	-1		4	1
4	-1		5	1
5	1		10	1
6	-1		11	1
7	-1		16	1
8	-1
9	-1
10	1
11	1
12	1
13	1
14	1
15	1
16	1
17	1
18	1
19	0
20	1
21	1
22	1
23	1
24	1
25	-1
26	-1
27	-1
28	-1
29	1
30	1
31	1
32	1
33	1
34	1
35	1
36	1
37	-1
38	-1
39	1
40	1
41	1
42	1
43	1
44	1
45	1
46	1
47	1
48	-1
49	-1
50	-1
51	-1
52	-1
53	1
54	-1
55	-1
56	-1
57	-1
58	-1
59	-1
60	1
61	1
62	-1
63	-1
64	-1
65	-1
66	-1
67	1
68	1
69	1
70	-1
71	-1
72	-1
73	-1
74	-1
75	-1
76	-1
77	-1
78	-1
79	-1
80	1
81	1
82	1
83	1
84	1
85	1
86	1
87	1
88	1
89	1
90	1
91	1
92	1
93	1
94	-1
95	-1
96	1
97	1
98	1
99	1
100	1
Sheet2

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]

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]

One way with formulas:

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	-1			Sequence combination
2	-1		Position	3	4	5	6	7	8	9	10	11	12	13
3	-1		1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
4	-1		2	1	1	1	1	1	1	1	1	1	1	1
5	1		3	-1	1	1	1	1	1	1	1	1	1	1
6	-1		4		-1	1	1	1	1	1	1	1	1	1
7	-1		5			-1	1	1	1	1	1	1	1	1
8	-1		6				-1	1	1	1	1	1	1	1
9	-1		7					-1	1	1	1	1	1	1
10	1		8						-1	1	1	1	1	1
11	1		9							-1	1	1	1	1
12	1		10								-1	1	1	1
13	1		11									-1	1	1
14	1		12										-1	1
15	1		13											-1
16	1		Count:	2	1	1	?	?	?	?	?	?	?	?
17	1
Sheet4

Cell Formulas
Range	Formula
D16	D16	=SUMPRODUCT(--((A1:A98=D3)+(A2:A99=D4)+(A3:A100=D5)=3))
E16	E16	=SUMPRODUCT(--((A1:A97=E3)+(A2:A98=E4)+(A3:A99=E5)+(A4:A100=E6)=4))
F16	F16	=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]

SEQ Pattern.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	-1			Sequence combination
2	-1		Position	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
3	-1		1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
4	-1		2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5	1		3	-1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
6	-1		4		-1	1	1	1	1	1	1	1	1	1	1	1	1	1
7	-1		5			-1	1	1	1	1	1	1	1	1	1	1	1	1
8	-1		6				-1	1	1	1	1	1	1	1	1	1	1	1
9	-1		7					-1	1	1	1	1	1	1	1	1	1	1
10	1		8						-1	1	1	1	1	1	1	1	1	1
11	1		9							-1	1	1	1	1	1	1	1	1
12	1		10								-1	1	1	1	1	1	1	1
13	1		11									-1	1	1	1	1	1	1
14	1		12										-1	1	1	1	1	1
15	1		13											-1	1	1	1	1
16	1		14												-1	1	1	1
17	1		15													-1	1	1
18	1		16														-1	1
19	0		17															-1
20	1		Count:	2	1	1	0	0	0	0	1	1	0	0	0	0	1	0
21	1
22	1
23	1
24	1
25	-1
26	-1
27	-1
28	-1
29	1
30	1
31	1
32	1
33	1
34	1
35	1
36	1
37	-1
38	-1
39	1
40	1
41	1
42	1
43	1
44	1
45	1
46	1
47	1
48	-1
49	-1
50	-1
51	-1
52	-1
53	1
54	-1
55	-1
56	-1
57	-1
58	-1
59	-1
60	1
61	1
62	-1
63	-1
64	-1
65	-1
66	-1
67	1
68	1
69	1
70	-1
71	-1
72	-1
73	-1
74	-1
75	-1
76	-1
77	-1
78	-1
79	-1
80	1
81	1
82	1
83	1
84	1
85	1
86	1
87	1
88	1
89	1
90	1
91	1
92	1
93	1
94	-1
95	-1
96	1
97	1
98	1
99	1
100	1
Sheet3

Cell Formulas
Range	Formula
D20:R20	D20	=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]

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

Dynamic functions.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	-1			Sequence combination
2	-1		Position	3	4	5	6	7	8	9	10	11	12	13
3	-1		1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
4	-1		2	1	1	1	1	1	1	1	1	1	1	1
5	1		3	-1	1	1	1	1	1	1	1	1	1	1
6	-1		4		-1	1	1	1	1	1	1	1	1	1
7	-1		5			-1	1	1	1	1	1	1	1	1
8	-1		6				-1	1	1	1	1	1	1	1
9	-1		7					-1	1	1	1	1	1	1
10	1		8						-1	1	1	1	1	1
11	1		9							-1	1	1	1	1
12	1		10								-1	1	1	1
13	1		11									-1	1	1
14	1		12										-1	1
15	1		13											-1
16	1		Count:	2	1	1	?	?	?	?	?	?	?	?
17	1			2	1	1	0	0	0	0	1	1	0	0
18	1
Sheet4

Cell Formulas
Range	Formula
D16	D16	=SUMPRODUCT(--((A1:A98=D3)+(A2:A99=D4)+(A3:A100=D5)=3))
E16	E16	=SUMPRODUCT(--((A1:A97=E3)+(A2:A98=E4)+(A3:A99=E5)+(A4:A100=E6)=4))
F16	F16	=SUMPRODUCT(--((A1:A96=F3)+(A2:A97=F4)+(A3:A98=F5)+(A4:A99=F6)+(A5:A100=F7)=5))
D17:N17	D17	=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]

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

SEQ Pattern.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1		Sequence combination
2	Position	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
3	1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
4	2	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5	3	-1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
6	4		-1	1	1	1	1	1	1	1	1	1	1	1	1	1
7	5			-1	1	1	1	1	1	1	1	1	1	1	1	1
8	6				-1	1	1	1	1	1	1	1	1	1	1	1
9	7					-1	1	1	1	1	1	1	1	1	1	1
10	8						-1	1	1	1	1	1	1	1	1	1
11	9							-1	1	1	1	1	1	1	1	1
12	10								-1	1	1	1	1	1	1	1
13	11									-1	1	1	1	1	1	1
14	12										-1	1	1	1	1	1
15	13											-1	1	1	1	1
16	14												-1	1	1	1
17	15													-1	1	1
18	16														-1	1
19	17															-1
20	Count:	2	1	1	0	0	0	0	1	1	0	0	0	0	1	0
Sheet3

Cell Formulas
Range	Formula
D20:R20	D20	=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]

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]

Just for kicks, a way using Power Query as well.

SEQ Pattern.xlsm
	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17
2	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1	-1
3	1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
4	-1	1	1	1	1	1	1	1	1	1	1	1	1	1	1
5		-1	1	1	1	1	1	1	1	1	1	1	1	1	1
6			-1	1	1	1	1	1	1	1	1	1	1	1	1
7				-1	1	1	1	1	1	1	1	1	1	1	1
8					-1	1	1	1	1	1	1	1	1	1	1
9						-1	1	1	1	1	1	1	1	1	1
10							-1	1	1	1	1	1	1	1	1
11								-1	1	1	1	1	1	1	1
12									-1	1	1	1	1	1	1
13										-1	1	1	1	1	1
14											-1	1	1	1	1
15												-1	1	1	1
16													-1	1	1
17														-1	1
18															-1
19	2	1	1	0	0	0	0	1	1	0	0	0	0	1	0
Sheet1

let
    Source = Excel.CurrentWorkbook(){[Name="Table2"]}[Content],
    Demote = Table.DemoteHeaders(Source),
    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),
    Promote = Table.PromoteHeaders(TransposeBack, [PromoteAllScalars=true])
in
    Promote

 

[Sinuhet]

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.