Concatenate multiple cells in one cell based on ID

Question

I'm trying to figure out a formula for concatenating data in cells based on a condition. Specifically, I need to output rows for each unique "Conversation ID" (found in column C) and concatenate the messages (column D) in one of the cells in that row based on a unique identifier ("Conversation ID").

The messages are chronologically ordered (just for reference). The data that I would like to concatenate takes input from "Sender", "Conversation ID" (i.e. unique identifier), and message(s) and outputs that in the following form:

The google sheet is found here: https://docs.google.com/spreadsheets/d/1cza9uBiVuXSFqGRt-sQaVK76qfpdWnbxHGNe7_Dif80/edit?usp=sharing

Answer 1

Combine the "Sender" and "Message"

Enter this formula in Cell F1:
=QUERY({"Sender:Message"," ";ArrayFormula({B2:B11}&":'"&{D2:D11}&"'"),C2:C11},"Select Col1 Where Col2<>'' ")

Get the Unique 'Conversation ID'

Enter this formula in cell H1:
={"Conversation ID";unique(C2:C11)}

Build the Conversations by unique ID

Enter this formula in Cell I2:
=iferror(textjoin( char(10), true, filter(F2:F11, C$2:C$11 = H2) ))

The copy down to row#4 (for each unique 'Conversation ID')

---

---

References:

Webapps: Google SpreadSheets query: how to join "grouped by" cells in a list? - @doubleunary
WebApps: Concatenating two columns using only filled cells in one of them - @Rubén