Concatenate multiple rows

[chris4987]

Hi! I have the below Input and Output data for possibly concatenating across Multiple Rows. I am not sure that fits this workflow, but looking for any guidance! Thanks

Input

Client Name	Address	Duplicate
James	123 Starlight Lane	Yes
Christopher	123 Starlight Lane	Yes
Steven	456 Milky Way	Yes
David	456 Milky Way	Yes
Jed	456 Milky Way	Yes
Nehemiah	789 Aurora Blvd	No
Sam	101 Dipper Street	No

Output

Client Name	Address	Mailing Label
James	123 Starlight Lane	James, Christopher
Christopher	123 Starlight Lane	James, Christopher
Steven	456 Milky Way	Steven, David, Jed
David	456 Milky Way	Steven, David, Jed
Jed	456 Milky Way	Steven, David, Jed
Nehemiah	789 Aurora Blvd
Sam	101 Dipper Street

[Luke_C]

Hi @chris4987 

The summarize tool should be able to help you. See below screenshot with configurations. It will let you concatenate based on common fields, in this case address. 

[messi007 · Accepted solution]

@chris4987,

Please see below 

I did it using the concatenate function in the summarize tool 🙂

Attached the workflow,

Hope this helps!

Regards

[AbhilashR]

Hi @chris4987, the one way I would attempt to solve this is to concatenate the Client Names using the Summarize tool and joining it back to the original dataset on Address. Attached is a baseline workflow for you to build upon.  

[chris4987]

Thank you! This worked perfectly

[vencric]

How to concatenate value in same row, till blank value is found. Ex.

Input:

Level   Prefix    Base         Concat

0          JH          1000  

1           KB         1001        KB-1001

2           BC         1002        BC-1002

1           CD         1003        CD-1003

0          AB          1004

1          AA          1005          AA-1005

Output:

Level   Prefix    Base         Concat                 OP

0          JH          1000                                     KB-1001;BC-1002;CD-1003

1           KB         1001        KB-1001

2           BC         1002        BC-1002

1           CD         1003        CD-1003

0          AB          1004                                     AA-1005

1          AA          1005          AA-1005

[AbhilashR]

HI @vencric, I would find a way to isolate sections of the data between two nulls (maybe using a multi-row formula tool) and then use the Concatenate option in the Summarize tool and think about joining back the summarized records to the source data. There are a few other ways you can approach this but hopefully this input helps you breakdown the problem into smaller steps.