Free Trial

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

How can I delete duplicate rows and merge columns?

Xiaorui
7 - Meteor

Xiaorui_0-1664441397905.png

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

Xiaorui_1-1664441486648.png

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

7 REPLIES 7
Christina_H
14 - Magnetar

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.

Christina_H_0-1664442055862.png

 

messi007
15 - Aurora
15 - Aurora

@Xiaorui,

 

Please see below :

 

messi007_0-1664442887528.png

 

Attached the workflow,

Regards,

Xiaorui
7 - Meteor

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
7 - Meteor

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

Xiaorui
7 - Meteor

  

Christina_H
14 - Magnetar

@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
13 - Pulsar

@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. 

Labels
Top Solution Authors