Let's say I have a survey of people in teams, and what fruits they like:

User  | Team  | Fruits
Dave  | Mine  | Banana, Apple
Steve | Mine  | Apple, Orange
Matt  | Yours | Banana

This would have been populated by a Google Form. I am looking to transform this into:

Team  | Fruits
Mine  | Banana, Apple, Orange
Yours | Banana

I'd then be able to know that two teams like bananas, while one team also likes apples and oranges. I have been looking into some combination of QUERY and JOIN but am getting pretty lost.

In one cell, I have done =UNIQUE('Raw Responses'!C2:C105), which gets me all the unique Teams.

In another, I've done =JOIN(", ",'Raw Responses'!E2, 'Raw Responses'!E3) gets me what I want manually (without the unique values bit yet, so I would see apples twice).

In my third test, I did =QUERY('Raw Responses'!C2:E105, "SELECT E where C='Mine'"), which spreads the answer across two rows

Note that C corresponds to Teams and E corresponds to Fruits in my actual sheet.


=JOIN(", ", QUERY('Raw Responses'!C2:E105, "SELECT E where C='Mine'")) almost is what I want. I am just not sure how to make Mine become a pointer of A1 inside a different sheet than Raw Responses as this is in Sheet2. I then need to make the cell be unique values only still.

Update 2 =JOIN(", ", QUERY('Raw Responses'!C2:E105, "SELECT E where C='" & $A1 & "'")) is working, just need to figure out unique values now :)


=JOIN(", ", UNIQUE(TRANSPOSE(SPLIT(JOIN(", ", QUERY('Raw Responses'!$C2:$BP105, "SELECT F where C='" & $A2 & "'")), ", ")))) solves it.

It basically transforms between columns and rows so I can use unique and then slap it all back.

  • JOIN formula will fail on joining more than 50000 characters, so here is the backup for that:
    • cell E2: =UNIQUE(B2:B)
    • cell F2 and drag down:

 IFERROR(TRIM(SPLIT($C$2:$C, ",")), $C$2:$C), )), , 999^99)), , 999^99), "♦")), "♠")), " ", "♥")), 
 "select Col2 where Col1 = '"&E2&"' and Col2 is not null", 0), , 999^99), " ", ", "), "♥", " "))


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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