Hi,
Can someone please help, I have dataset in text format which I cleaned it and I got one column in my result. Now I am trying to combine multiple rows into one based on condition. Below is my sample data set:
I have one column, based on data I want to combine rows based on keyword i.e. combine rows starting from keyword" job details" until another row comes with "job details" keywords and so on:
Before:
xyz job details |
Default Name: XYZ |
Job Title: XYZ report |
xya@g.com |
abc@g.com |
abc job details |
Default Name: abc |
Job Title: abc report |
jdhsgkufd@g.com |
gjfdgd@g.com |
After:
xyz job details; Default Name: XYZ;Job Title: XYZ report;xya@g.com;abc@g.com |
abc job details;Default Name: abc;Job Title: abc report;jdhsgkufd@g.com;gjfdgd@g.com |
Once I get this I'll see how to move it to different columns.
I would really appreciate any help on how to accomplishing this. Thank you!
Solved! Go to Solution.
Hi @Yesha, you could use a multi-row formula tool to group Xyz and abc jobs as separate group, after which a Summarize tool can help concatenate the strings.
Hope this helps.
Here you go. Workflow is attached. The trick is using the formula tool to create a flag whenever it finds text that says job details which indicates the next block of records. Then you use the running total on the flag to group those records together. Summarize allows you to group by the running total flag, and you can concatenate on the field. Then finally you can use a text to columns to split on the comma that was used in the concatenation.
Thank you Abhliash. I really appreciate!
Thank you Brandon!!! It was my first post and I really appreciate this community.
Hi @Yesha, as @BrandonB suggested, you could use the TextToColumn tool to parse out the individual components of the concatenated data. But in many instances we may not know how many columns to parse (or it may vary by different groups), in those cases I generally use a macro approach to parse out the data.
I have attached an updated sample solution for you to look at. Thank you for reaching out to the Community!