Collate (or Aggragate?) Text Cells from Multiple Rows in Google Sheets

[GSKov]

Hi there. I'm not even sure the correct terminology to describe this request. I have a set of data in which one student may have submitted one or more teacher names in a Google Form. What I hope to accomplish is to get the Unique list of teacher names, and then develop a list of every student who chose each teacher. Sheet 1 HERE shows a sample of the data, with Column A as Student name and Column B with comma-separated Teacher names.

I pulled the Unique list of Teacher names into Column G of Sheet1, thinking that might serve as a helper column somehow. Sheet2 shows the output I'd like to generate. Can anyone point me in the right direction of how I might finalize this? Thanks for reading!

Cheers,
Kov

 

[Fluff]

How about

=textjoin(", ",,filter(A2:A7,isnumber(search(G2,B2:B7))))

 

[GSKov]

That's brilliant, thank you! I had tried some Filter options, but I don't think they were anywhere close.

I did realize I have another issue. It's possible that the student data may not have any teachers selected. For example, if B8 is blank for the student response, the array in G2 gets #VALUE! on its top row. I guess this is because I used is not null in the Query part of that formula, but I'm not having any luck coming up with how to avoid that. If anyone decides to take a look, thanks in advance!

Cheers,
Kov

 

[Fluff]

I don't know enough about Sheets to help with that.