0

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").

input

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:

output

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

0

1 Answer 1

0

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')


screenshot


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

1
  • Thanks, Tedinoz, it works very well! Mar 27, 2022 at 20:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.