Identify 2 or 3 Conescutives in a string of data

[ctrapper]

I'll do my best to explain what I am having problems with. I have a formula that is showing me how many times either an A or B appears in succession in Row E of my worksheet. so for example:

Col E Col F
A
A
A
B 3
A 1

this works great, however I have come to realize I need to also account for situations where the succession is longer than 2 or 3. Here is an example, and the data situation I am having issues solving:

Col E
A
A
A 2
A 3
A
A

I want to be able to put a formula in that only counts 2 and/or 3 consecutive A's or B's. Is this enough info? Only A and B exist in Col E.

[teylyn]

what's the formula you're using so far?

[ctrapper]

=IF(E3=E2,"",COUNTIF(INDEX(E$1:$E1,MATCH(9.99999999E+307,$F$1:$F1)):E2,E2))

The problem I am having is that this formula does what it was intended, I wanted to do a count to see all the consecutive occurrences of A and B. I.E. how many times did A show up 5 conesecutive times, or B 8 consecutive etc. I am able to pull 2 and 3 out of this formula, as A and B do occur 2 and 3 times in a row, but I also need to account where they occurred 2 or 3 times and it turned into a longer string. Make sense?

[DonkeyOte]

IMO, a sample file with expected results might help clarify a few things.

[ctrapper]

Here is an example.. hope this helps

[DonkeyOte]

Does the sheet represent the desired output ?

Once a streak has reached three is the streak is exempt from further analysis or does it reset ? To confirm by means of example - what would:

A
A
A
A
A
A

result in ?

A
A 2
A 3
A
A
A

or

A
A 2
A 3
A
A 2
A 3

[ctrapper]

This is the desired output, so yes, once it get's to 3 max, that negates any further analysis of that string. Thanks!

A
A 2
A 3
A
A
A

[DonkeyOte]

Using the sample file:

Please Login or Register  to view this content.

though I should add that I don't necessarily concur with your expected results... based on my understanding:

F10 should be 2 rather than F11
F14 should be 2 rather than F15
F36 should be 2 rather than Null

edit: I am sure with more thought the above can be simplified ... it's not efficient.

[DonkeyOte]

edit: in hindsight there's no need for the COUNTIF overhead given the same result can be achieved using just the use ROW difference

Please Login or Register  to view this content.

[ctrapper]

This works perfect.. you are correct..the ones you pointed out were a typo. The formula put's them in the correct spot! Thanks a ton!