Combine duplicates into single row, contatenate other columns if needed

[mrc482]

Hi All,

My problem might have a simple solution, but I am new to Alteryx and all my attempts failed.

I have a dataset with some request ID's in first column that are duplicated there and I want to summarize the data using this column, but with following requirements:

• If in other columns data are the same for each ID, then I want this common value to stay in this column as it is
• If in other columns the data differs then I want them to be concatenated with a comma

The below table illustrates what I want to achieve:

Input:

Request ID	Company name	Country	Date	Staff Name	Reviewer name
A1	Company A	DE	30.05.2023	Elisabeth Chen	Melanie Wood
A1	Company A	DE	30.05.2023	John Smith	Melanie Wood
A2	Company B	UK	31.12.2023	Erica James	Dan Salmon
A2	Company B	UK	31.12.2023	Erica James	Maggie Clark
A2	Company B	UK	30.06.2023	Erica James	Sarah Johnson
A3	Company C	ES	14.05.2023	Sandra Pink	Paul Griffin
A3	Company C	ES	14.05.2023	Sandra Pink	Paul Griffin

Outcome that I would like to get:

Request ID	Company name	Country	Date	Staff Name	Reviewer name
A1	Company A	DE	30.05.2023	Elisabeth Chen, John Smith	Melanie Wood
A2	Company B	UK	31.12.2023, 30.06.2023	Erica James	Dan Salmon, Maggie Clark, Sarah Johnson
A3	Company C	ES	14.05.2023	Sandra Pink	Paul Griffin

I tried to use Summarize tool, but the data are always contatentaing, for example for ID request A2 I had "Erica James, Erica James, Erica James" in Staff Name column and I want to avoid it

Thanks in advance

[binuacs · Accepted solution]

@mrc482 One way of doing this

[Yoshiro_Fujimori]

@binuacs ,

Your solution is very elegant. Thank you for sharing this! (I am not good at Tile tool...)

@mrc482 ,

Here's an alternative way of doing this (not so elegant one. Just FYI😉)

[gaoa]

@mrc482 Here's my attempt at the question. 😊

[flying008]

Hi, @mrc482 

Another way by use a macro of gallery:

[mrc482]

@binuacs Thanks for your solution, it works perfectly and I will recreate it in my workflow! 

@flying008

@gaoa

@Yoshiro_Fujimori 

Thank you all for your solutions, I analyzed all of them and it is very educational for me :)