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

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

Datasets

Help

Accepted answers

Christina_H

It depends on what else is in your data, but this works for the examples given:

if [Data that I need to group]>"*" and [Data that I need to group]<"*d" then "*( - *C Group" else "" endif

or if you need to be more specific:

if left([Data that I need to group],2) in("*(","*A","*B","*C") then "*( - *C Group" else "" endif

All comments

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,

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

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?

Christina_H

It depends on what else is in your data, but this works for the examples given:

if [Data that I need to group]>"*" and [Data that I need to group]<"*d" then "*( - *C Group" else "" endif

or if you need to be more specific:

if left([Data that I need to group],2) in("*(","*A","*B","*C") then "*( - *C Group" else "" endif

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15874

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703