Count repeats of the Maximum number in each consecutive count

[granzowd]

Hi,
excel sample
In the example I have a column of only 3 words here / there / everywhere these run as results
Note: The column I have is large, and I need an easier method, then pen and paper.
These words “may” come in continuous repeated results.
Currently I count the "no appearance" of these continuous results.
So when it says “here”, then we know “there” and “everywhere” do not appear, and then they get a count of 1, the number increases as the text does not appear.
I now know is when a word does NOT appear (totally absent) for XX times.
Note: My count has determined the MAXimum amount of times that the text does NOT appear is 31 (Month), so a table starting at 31 and counting down

I would like to create a table that display how many times in the string this happens
So:
“here”
Not appeared 31 times in consecutive rows in the column XX times
Not appeared 30 times in consecutive rows in the column XX times
Not appeared 29 times in consecutive rows in the column XX times
Not appeared 28 times in consecutive rows in the column XX times
Not appeared 27 times in consecutive rows in the column XX times
Not appeared 26 times in consecutive rows in the column XX times
Not appeared 25 times in consecutive rows in the column XX times
Not appeared 24 times in consecutive rows in the column XX times
Not appeared 23 times in consecutive rows in the column XX times
Etc (all the way to 1)

And repeat in a table for the other 2 text words “there” and “everywhere” as well. (See Attached)

In my attached example currently I look in each column and write down the number highest number time a text is not present.

So if you look at “here” it counted a string of
3 has happenned 5 times and 4 has happenned 1 time and 12 has happenned 1 time

“there” 3 has happenned 2 times and 6 has happenned 2 times

“everywhere”
7 has happenned 2 times
8 has happenned 2 times
9 has happenned 1 time
18 has happenned 1 times

But as I explained in the total string of the 9 that appeared once in “everywhere” it has 7 and 8 in that string when counting up to 9
So I cant use a formula with an if exist or countif outcome.

Ideally instead of having my 3 columns counting just a formula that populates with a table appearance. Or I could hide those current counting columns too.

I normally work out most problems using this forum..but I am at a loss what the formula is called

David

 

[Peter_SSs]

Welcome to the MrExcel board!

Is this, copied across and down, what you want?

granzowd 1.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1
2	here
3	here
4	here
5	everywhere
6	here
7	here
8	there
9	there
10	there
11	here
12	there
13	there
14	there
15	everywhere
16	here
17	here
18	there
19	here
20	there
21	there
22	here
23	everywhere
24	here
25	here
26	there
27	there
28	there
29	here
30	there
31	there
32	everywhere
33	here
34	here
35	there
36	here
37	there
38	there
39	here
40	everywhere
41	here
42	here
43	there
44	there
45	there
46	here
47	here
48	here
49	everywhere
50	here
51	here
52	there
53	there
54	there
55	here
56	there
57	there
58	there
59	there
60	there
61	there
62	there
63	there
64	there
65	there
66	there
67	there
68
69
70		30	29	28	27	26	25	24	23	22	21	20	19	18	17	16	15	14	13	12	11	10	9	8	7	6	5	4	3	2	1
71	here	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	1	5	2	6
72	there	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	2	0	2	2	0	5
73	everywhere	0	0	0	0	0	0	0	0	0	0	0	0	1	0	0	0	0	0	0	0	0	1	2	2	0	0	0	1	0	0
Sheet1

Cell Formulas
Range	Formula
B71:AE73	B71	=SUM(--(FREQUENCY(IF($A$2:$A$67<>$A71,ROW($A$2:$A$67)),IF($A$2:$A$67=$A71,ROW($A$2:$A$67)))=B$70))

 

[granzowd]

FANTASTIC, I wasn't sure it was possible..
But you did it
Thanks heaps

 

[Peter_SSs]

You're welcome. Thanks for the follow-up.