Use COL name to Get ROW names based on whether a value exists in a Column with the specified Name-CodePudding

Would deeply appreciate it if anyone could help me with this.

Overview

In a google sheet I have the following grid:

	School A	School B	School C
Tim	x		x
John
Martin	x	x
Jack			x

The Rows are Names of people who cater to certain schools. The value 'x' in the cell simply signifies the relation. So Tim caters to School A & School C and similarly Jack only caters to School C.

Note: The cells are either empty or contain that 'x'. Thought that might help. We don't really need to look for 'x' just a non-empty cell.

Question

I have another table like follows, where I have a School column listing all the schools in rows.
I would like to create a formula to use the table above and return a concatenated string listing all the people catering to that school.

School	People
School A	Tim, Martin
School B	Martin
School C	Tim, Jack

CodePudding user response：

use:

=INDEX(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(SPLIT(FLATTEN(
 IF(B2:D="",,B1:D1&"♠♦"&A2:A&",♦"&A2:A)), "♦"), 
 "select Col1,max(Col2) where Col2 is not nUll group by Col1 pivot Col3"), 
 "offset 1", 0)),,9^9)), "♠")), ",$", ))

CodePudding user response：

or:

=INDEX(REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(
 {B1:D1&"♦"; IF(B2:D="",,A2:A&",")},,9^9)), "♦")), ",$", ))