How can I delete duplicate rows and merge columns?

[Xiaorui]

My data is kind similar to this one, I want to delate the duplicate rows and also merge columns, like below.

I tried to use unique tools, it can delate the identical ones, Joe, but I don't know how to deal with Lisa here

[Christina_H · Accepted solution]

You can use a summarize tool to concatenate the values for Lisa, however you also need to do some cleaning up for Joe (unless you're happy with him having two rows?)

Use a text-to-columns tool to split the Prefer column to rows, then data cleansing to remove leading/trailing white space and convert to title case.  Then you can use a unique tool to remove duplicate rows and a summarize tool to concatenate onto a single row per person.

[messi007 · Accepted solution]

@Xiaorui,

Please see below :

Attached the workflow,

Regards,

[Xiaorui]

Hi,

Thanks for your reply and it does work well, but I'm very new to this, so get a bit lost here, could you please explain the logic here?  I'm not sure the muti-row formula tool

Many thanks

[Xiaorui]

when you use summarize tool to concatenate to one single row for each person, could you explain a bit how did you select columns? I'm trying to do it with hundreds rows and stuck here

Many thanks

[Christina_H · Accepted solution]

@Xiaorui See my workflow attached.  For the summarize tool I selected the Code and Name columns at the top (use ctrl to select multiple or you can click and drag), then Group By from the Add dropdown.  Then select the Prefer column and go down to String under the Add dropdown to find Concatenate.

[FrederikE]

@Xiaorui,

The Multi-Row Tool of @messi007 does always compare if the previous row already had the same input in the "prefer" column. 

By using "contains" instead of a normal comparison (=) it also works when the string doesn't fully match, but only parts of it.

E.g.

'apple'

'apple, peach'

match then.