Search code examples
google-sheetsfilteruniquetransposegoogle-sheets-query

How do I get a function to return values in adjacent cells instead of being printed below the cell where the function is written?

I have a table of teams, names, and roles. I am trying to move that data to a separate table, but I want to write a query that returns the values in adjacent cells as opposed to directly underneath which I think is the default.

Here is a picture of the table

0

=QUERY(A2:C9, "select B where A = 'Bears' and C = 'Coach'", 0)

// returns coach name for team 'bears'

=QUERY(A2:C9, "select B where A = 'Bears' and C = 'Player'", 0)

// returns all player names for team 'bears'

The first query achieves what I want because there is only one coach on each team to return, but the second query does not achieve what I want which is to return the values sideways to so fill in the Player1-3 columns and not impede the row beneath.

Solution

  • delete everything in range E2:I. then...

    paste in E2 cell:

    =UNIQUE(A2:A)

    paste in F2 cell and drag down:

    =FILTER(B$2:B, A$2:A=E2, C$2:C="coach")

    paste in G2 cell and drag down:

    =TRANSPOSE(FILTER(B$2:B, A$2:A=E2, C$2:C="player"))

    0

    UPDATE:

    =TRANSPOSE(QUERY(Players!A:I, "select I where G = 'player' and A = "&
 QUERY(Teams!A:F,   "select A where F = '"&C2&"'", 0), 0))