How to merge multiple rows from one column

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!

Join

Data Investigation

Accepted answers

AbhilashR

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.

sample.yxmd

BrandonB

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.

example.yxmd

All comments

AbhilashR

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.

Thank you Abhliash. I really appreciate!

Yesha

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

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167