1

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.

Improve this question

2 Answers 2

Reset to default
2

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)), " "))

enter image description here

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

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&"'"))
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.