How to merge multiple rows from one column

[Yesha]

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!

[AbhilashR · Accepted 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.

[BrandonB · Accepted solution]

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. 

 

[Yesha]

Thank you Abhliash. I really appreciate!

[Yesha]

Thank you Brandon!!! It was my first post and I really appreciate this community.

[AbhilashR]

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!