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 

[Arothman12]

For now yes I will expand it once i get this first grouping to work.

[CarliE]

@Arothman12 ,

Attached is the workflow. There are 2 containers -- the top considers other letters based off the first 2 char. The bottom container considers only your first grouping.

Please mark as a solution if this helped you.

[Arothman12]

Unfortunately this seems a bit more complex than I think it needs to be I just need a formula that that takes the data from that one column and checks its first two characters depending on what they are then creating in a new column a tag like *( - C Group which is named after the range of cells that the formula is grouping so we had in the first example the range being *(, *A, *B and *C. So the IF statement would say IF column A where the data is has the first two characters mentioned just before create in the new column the tag *( - *C Group. 

So I envisioned a IF LEFT formula that said if column A which we can call EGA contains a cell that starts with *( through a cell that starts with *C then "*( - *C Group ELSE for the time being "N/A" ENDIF

[Christina_H · Accepted solution]

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

[DawnDuong]

Hi @Arothman12 

I think this gives the result you need. If you want to make this grouping more dynamic, you need to set the threshold conditions - but that's a separate topic i guess.

The key here is that you want to include in your workflow:

1) Sorted value

2) extract the first char and the 2nd char

3) set a threshold for the grouping (i.e. between named vs. N/A in this case)

4) use the "+" concatenate operator to get the text output in your new column.

Dawn