Count repeats of the Maximum number in each consecutive count

granzowd

New Member
Joined
Oct 1, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,836
Office Version
  1. 365
Platform
  1. Windows
Welcome to the MrExcel board!

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

granzowd 1.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAE
1
2here
3here
4here
5everywhere
6here
7here
8there
9there
10there
11here
12there
13there
14there
15everywhere
16here
17here
18there
19here
20there
21there
22here
23everywhere
24here
25here
26there
27there
28there
29here
30there
31there
32everywhere
33here
34here
35there
36here
37there
38there
39here
40everywhere
41here
42here
43there
44there
45there
46here
47here
48here
49everywhere
50here
51here
52there
53there
54there
55here
56there
57there
58there
59there
60there
61there
62there
63there
64there
65there
66there
67there
68
69
70302928272625242322212019181716151413121110987654321
71here000000000000000000100000001526
72there000000000000000000000000202205
73everywhere000000000000100000000122000100
Sheet1
Cell Formulas
RangeFormula
B71:AE73B71=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

New Member
Joined
Oct 1, 2020
Messages
2
Office Version
  1. 2019
Platform
  1. Windows
FANTASTIC, I wasn't sure it was possible..
But you did it
Thanks heaps
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
46,836
Office Version
  1. 365
Platform
  1. Windows
You're welcome. Thanks for the follow-up. :)
 

Watch MrExcel Video

Forum statistics

Threads
1,113,928
Messages
5,545,080
Members
410,652
Latest member
Zot
Top