group cells together that have the same text in the cell beside it-CodePudding

i have two columns in a spreadsheet in google sheets like below: (with much more data & matching teams, this is just a sample)

PLAYER      PUBLIC TEAM
Kamiunist   Teddy Tactics 
saurav      UT Dallas 
PNW DeadEye Purdue Northwest
ricefarmer2003  White Lotus 
NELBLOOD18  REIGN GC
PSISTM lazy PSISTORM
Katarina    VersionX
Jeorge      University of Calgary 
florescent  Divine Angels

i am trying to group together the cells that have the same "Public Team" value, to look like below

Team        Player 1    Player 2    Player 3    Player 4    Player 5
This team   one         two         three       four        five

i am looking to either do a formula or a script

i tried to use a vlookup but i believe that it would list the teams over and over again once the formula comes up on a new player with the same team

CodePudding user response：

Assuming that the players are in column A2:A and teams in column B2:B, use filter(), like this:

=byrow( 
  unique(B2:B), 
  lambda( 
    team, 
    { team, transpose( iferror( filter(A2:A, len(A2:A), B2:B = team) ) ) }
  ) 
)