Splitting one column in two while keeping values grouped together

Question

Hi Community,

I have an input data set, that contains data for group names and it's members in a single column. I'd like to split the data into two columns so that the group name is in the column1 and group member is in the column2. Only difference, or pattern I was able to find is, that group names are always in UPPER case only letters, while members are never upper case only. I was able to split the data into two columns using Regex and Filter tool, but it didn't really solve my problem, because I need to keep the Group - member relationship. Please see the sample of Data input and desired output below:

Data input:

Column1ABC_GROUPMemberBCD_GROUPMemberMemBer2DEF_GROUPMEMber3member5

Desired output:

Column1Column2ABC_GROUPMemberBCD_GROUPMemberBCD_GROUPMemBer2DEF_GROUPMEMber3DEF_GROUPmember5

Thank you for all your suggestions!

Jonathan-Sherman · Accepted Answer

Hi @Machal,

I think this is what you're trying to achieve? You'll want to pivot your data - using the crosstab tool in this case

If this solves your issue please mark the answer as correct, if not let me know! I've attached my workflow for you to download if needed.

Regards,

Jonathan