Splitting one column in two while keeping values grouped together

[Machal]

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:

Column1

ABC_GROUP

Member

BCD_GROUP

Member

MemBer2

DEF_GROUP

MEMber3

member5

Desired output:

Column1	Column2
ABC_GROUP	Member
BCD_GROUP	Member
BCD_GROUP	MemBer2
DEF_GROUP	MEMber3
DEF_GROUP	member5

Thank you for all your suggestions!

[Jonathan-Sherman · Accepted solution]

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

[Machal]

Thank you very much for such a quick answer @Jonathan-Sherman that's exactly what I needed!

I'll definitely need to wrap my head around how the Crosstab in combination with Multirow formula tool works - the workflow you provided will definitely help me in that regard.

Have a great day!