Hi,
I have attached a sample that should shelp the explanation.
In this example I have a column of only 3 words here / there / everywhere
The column is large, and I need an easier method.
These words “may” come in continuous strings.
Currently I count these continuous strings.
So when it says “here”, then we know “there” and “everywhere” do not appear.
What I want to 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 30
I would like to create a table that display how many times in the string this happens
So:
“here”
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 (to 1)
And repeat in a table for “there” and “everywhere” as well. (See Attached)
In my attached example currently I look in each column and write down the number
The number continues to count up as the text is absent.
Countif won’t work because the string ended at a count of 30 and 30 column has 29 28 27 26 etc.
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
Bookmarks