Looking for patterns in data set

[fatterthanelvis]

In column A I have a set of cells with either a L, H or blank Value. In column B I have 256 different patterns of possible consecutive L and H combinations to a run of 8. I want to find out how many times each of those pattern combinations show up in column A. xlsx

[shg]

What's the relevance of the blanks? Do they interrupt the pattern, or get ignored?

[fatterthanelvis]

the blanks interrupt the pattern

[shg]

Assuming they interrupt it,

	A	B	C	D	E	F	G
1	L		LLLLLLLL	0	203		E1:E24: {=FREQUENCY(B1:B58728, D1:D256)}
2	L		LLLLLLLH	1	227
3	H		LLLLLLHL	2	217
4	H		LLLLLLHH	3	235
5	H		LLLLLHLL	4	219
6	H		LLLLLHLH	5	207
7	H		LLLLLHHL	6	211
8	L	124	LLLLLHHH	7	229		B8 and down: =IF(COUNTA(A1:A8)<>8, "", SUMPRODUCT((A1:A8="H") * 2^{0;1;2;3;4;5;6;7}))
9	H	190	LLLLHLLL	8	209
10	L	95	LLLLHLLH	9	246
11	H	175	LLLLHLHL	10	199
12	L	87	LLLLHLHH	11	212
13	L	43	LLLLHHLL	12	218
14	H	149	LLLLHHLH	13	205
15	L	74	LLLLHHHL	14	248
16	L	37	LLLLHHHH	15	206
17	L	18	LLLHLLLL	16	214
18	L	9	LLLHLLLH	17	183
19	L	4	LLLHLLHL	18	235
20	L	2	LLLHLLHH	19	226
21	L	1	LLLHLHLL	20	216
22	L	0	LLLHLHLH	21	192
23	H	128	LLLHLHHL	22	204
24	H	192	LLLHLHHH	23	214

[fatterthanelvis]

I think I misunderstood how to enter this in my table. Please excuse my lack of knowledge. I moved the B column of patterns over to the C column. Then I entered a number range from 0 - 255 in the D column. After that I had pasted =FREQUENCY(B1:B58728, D1:D256) without changing the formatting from E1 to E24. Then I pasted =IF(COUNTA(A1:A8)<>8, "", SUMPRODUCT((A1:A8="H") * 2^{0;1;2;3;4;5;6;7})) from B8 to B58728 allowing the formula to change format as it goes down the list. In my E column it populated the number 14, where as in your example it seems to show how many times the differnt patterns repeat in the set of B1 to B58728.

[shg]

The workbook has a temporary home at https://app.box.com/s/f2c155ffe8c327dc7237

[fatterthanelvis]

Thank you so much!

[Crooza]

A slightly different solution. without helper columns but I'm getting different results to SHG probably because I'm ignoring the blanks rather than letting them interrupt the pattern. I've assumed no more than 4 blank rows in any series of 8 (hence looking at a rolling series of 12 rows)

=SUMPRODUCT(--(B1=LEFT($A$1:$A$58721&$A$2:$A$58722&$A$3:$A$58723&$A$4:$A$58724&$A$5:$A$58725&$A$6:$A$58726&$A$7:$A$58727&$A$8:$A$58728&$A$9:$A$58729&$A$10:$A$58730&$A$11:$A$58731&$A$12:$A$58732,8)))

[shg]

You're welcome.