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
C corresponds to
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.
=JOIN(", ", QUERY('Raw Responses'!C2:E105, "SELECT E where C='" & $A1 & "'")) is working, just need to figure out unique values now :)