Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isolating specific string combinations
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isolating specific string combinations
Try the below formulas in B1 and B2
In B1 =IF(AND(A1="AB",COUNTIF(A2:A5,"C*")=4),A1,"") In B2 =IF(AND(A2="AB",COUNTIF(A3:A6,"C*")=4),A2,IF(AND(B 1<"",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 "Demosthenes" wrote: 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? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isolating specific string combinations
Forgot to mention to copy the formula in B2 down to B3,B4... as required
-- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below formulas in B1 and B2 In B1 =IF(AND(A1="AB",COUNTIF(A2:A5,"C*")=4),A1,"") In B2 =IF(AND(A2="AB",COUNTIF(A3:A6,"C*")=4),A2,IF(AND(B 1<"",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 "Demosthenes" wrote: 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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Isolating specific string combinations
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. "Jacob Skaria" wrote: Forgot to mention to copy the formula in B2 down to B3,B4... as required -- If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Try the below formulas in B1 and B2 In B1 =IF(AND(A1="AB",COUNTIF(A2:A5,"C*")=4),A1,"") In B2 =IF(AND(A2="AB",COUNTIF(A3:A6,"C*")=4),A2,IF(AND(B 1<"",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 "Demosthenes" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Isolating string combinations | Excel Worksheet Functions | |||
Combinations in a string of data | Excel Worksheet Functions | |||
isolating specific list | Excel Discussion (Misc queries) | |||
isolating a string containing a % symbol | Excel Worksheet Functions | |||
Isolating a custom toolbar to a specific spreadsheet | Excel Discussion (Misc queries) |