Isolating specific string combinations

[Demosthenes]

This is a further iteration of a problem I've been working on and posted
earlier under the subject: "Isolating String Combinations."

Say you have the following data:

AB
CD
CD
CE
CD
AB
CD
AG
CD
CD
CE
CS
CD
AB
CD
CD
CF
CG
CE

Which can be in one or two columns. I'm trying to analyze the makeup of
the"CX" occurrences (i.e., whether they are CD, CF, CG, etc), but ONLY IN
CHAINS longer than 4 occurrences (i.e., 4 C's in a row or longer) AND that
follow "AB" (i.e., not "AG"). I want to create a helper column that is
composed of just these instances that I'm considering. So my question is: How
do I create a helper column that is composed of only the chains of 4 or
longer that follow "AB"? Output for the above would be:

AB
CD
CD
CE
CD

AB
CD
CD
CF
CG
CE

Thoughts?

 

[Jacob Skaria]

Try the below formulas in B1 and B2

In B1
=IF(AND(A1="AB",COUNTIF(A2:A5,"C*")=4),A1,"")

In B
=IF(AND(A2="AB",COUNTIF(A3:A6,"C*")=4),A2,IF(AND(B1<>"",COUNTIF(A2,"c*")=1),A2,""))

Col A Col B
AB AB
CD CD
CD CD
CE CE
CD CD
AB
CD
AG
CD
CD
CE
CS
CD
AB AB
CD CD
CD CD
CF CF
CG CG
CE CE


If this post helps click Yes

 

[Jacob Skaria]

Forgot to mention to copy the formula in B2 down to B3,B4... as required

 

[Demosthenes]

Jacob,

Thanks! Works great.

You don't need to use the 1st formula (B1) if you have a blank row at the
top of the column.