I'm struggling to find a solution on my own and I couldn't find a relevant answer for my specific case (or a similar one) so here I am. Before anything else I want to say thanks in advance for any help.

Here's my problem:

I have a Google Sheet with a table as follows:

[email protected]  |  [email protected]  |  [email protected]  |  [email protected]
[email protected]  |  [email protected]  |  [email protected]  |  [email protected]
...

So, basically it's a list of users in the first column and the groups that each user is a part of in the next columns. Each user is in more than one group.

Now I need to check which users are in each group (instead of checking in which groups each user is). I can easily flatten the range of groups and get the list of unique groups, but now I'm struggling to find a way to query the original table in order to get something like this:

[email protected]  |  [email protected]  |  [email protected]  |  [email protected]
[email protected]  |  [email protected]  |  [email protected]  |  [email protected]

Again, thanks for your help.

CodePudding user response：

try:

=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(A1:A9&"×"&B1:D9), "×"), 
 "select max(Col1) where Col2 is not null group by Col1 pivot Col2"),,9^9)), " "))

CodePudding user response：

Finally I tried a different approach:

I joined all the group names in a helper column (Column U) and then used the following formula:

=TRANSPOSE(QUERY('Domain 2'!$E$90:$U$167,"select E where U contains '"&L67&"'"))