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!
Solved! Go to 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
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!