Grouping a range of data based on first 2 characters found in another column

[Arothman12]

Hi Alteryx Community first time post so I apologize in advance if I don't properly word/format my post.

I am try to create a formula that looks at a range of strings in another column and based on the first two characters of each cell create a new column and label that group corresponding of cells with a new label. For example the first cell in the range starts with "*("  and then there are let's say 30 other cells that contain "*A" etc. until where I want to end that group at "*C"  and call that group "Group *( - "C".  How can I accomplish this? I tried several IF AND and IF LEFT formulas but couldn't get it to work I also tried REGEX_Match and couldn't get the formula right. Any assistance would be greatly appreciated.

-Avidan Rothman 

[CarliE]

@Arothman12,

Would you mind uploading dummy data / the expected output? This will help us to understand the process a little better.

Thanks!

[Arothman12]

Sure not a problem!

So the data I need grouped together based on the first two characters is on the left side and the outcome of the formula is in the right column.

So to clarify I want the formula to look at the left column and check the first two characters the cells and based on what the first two characters are create new column with what is written in the right column cells. Example below has cell A2 with the first two characters  "*(" so I want to look at all the below cells and once and check once it reaches the last cell with the first two characters "*C" to label those cells in the right column "*( - *C Group" and the process would repeat for subsequent rows.

Data that I need to group	Column with new name based on formula to group
*(abcdefg	*( - *C Group
*A list of examples	*( - *C Group
*B cell example	*( - *C Group
*C cell example	*( - *C Group
*D cell example

[CarliE]

@Arothman12,

How do you know that group D should not be within group C but *A,*B,*C should be?

[Arothman12]

Because the first groups range should only include strings that start with "*(" but end with "*C". "*D:" would be the beginning of the next group and would have the label in the next right column from the example above as "*D - *G Group".

[DawnDuong]

hi @Arothman12 

I read a couple of times but still did not 100% understand the logic. Is there a way to explain a bit more clearly?

Dawn.

[CarliE]

@Arothman12,

From assumptions I made, I created a workflow to group every 4 records and then named it the correct grouping. Attached is the workflow.

Please mark as a solution if this helped you so other members can benefit.

If this isn't the correct logic, please specify further. 

Thanks,

[Arothman12]

Sure

I want to create a formula that checks the first two characters of a range of cells and based on those two characters that start each cell create in a new column repeating output. So for example I have 4 cells in this order the fist one is *(abcd the next cell below in the column is *Aefghi the next cell below is *Bjklmo and the last cell is *Cpqrst. Based on those 4 cells I want the formula to look at each cells first two characters and based on what it sees create in a new column the tag *( - *C Group. So I will recreate the columns below. Because the range I want specified in the formula is containing any cells that  start with *( through *C should be called *( - *C in the newly created column

Data	New column created from formula
*(abcd	*( - *C Group
*Aefghi	*( - *C Group
*Bjklmo	*( - *C Group
*Cpqrst	*( - *C Group

[Arothman12]

I tried this formula but couldn't get the results I was looking for. IF REGEX_Match([EGA], "(*(|*A|*B|*C)) THEN "*( - *C Group" ELSE "N/A" ENDIF

[CarliE]

Ah Got it. So if its not in that grouping, it will be null?