CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulatransposearray-formulasgoogle-sheets-query
TC76
TC76

Reputation: 870

Transpose GROUPS of QUERY results to single column

I'm having difficulty TRANSPOSE'ing my QUERY results. The results are in groups of five and already transposed once from rows to columns. Now that each result is in columns of 5 rows, I want to combine those results to a single column, preferably with a space between each group.

Here is what I'm currently getting: enter image description here

And this is what I'm trying to achieve:

enter image description here

Here is the link to my sheet

Upvotes: 1

Views: 556

Answers (2)

MattKing
MattKing

Reputation: 7783

After figuring out a little more what you were going for on your sample sheet, I came up with this formula. it's a bit more complex than it would normally be for a retabulation like this because you wanted it limited to the first 5 players from your squad tab.

=ARRAYFORMULA(SUBSTITUTE(QUERY(VLOOKUP(SEQUENCE(COUNTA('Squad Compositions'!B4:B)*6,1,0)/6+4,{ROW('Squad Compositions'!B4:B),'Squad Compositions'!B4:B,ARRAY_CONSTRAIN(IFERROR(SPLIT(TRANSPOSE(TRIM(QUERY(TRANSPOSE(IF('Squad Compositions'!E4:S="",,'Squad Compositions'!E4:S&CHAR(10))),,15))),CHAR(10)&" ",0)),9^99,5),IF(ROW('Squad Compositions'!B4:B),"|")},MOD(SEQUENCE(COUNTA('Squad Compositions'!B4:B)6,1,0),6){0,1}+{2,3}),"where Col2 is not null"),"|",""))

Hope this helps.

Upvotes: 1

user11982798
user11982798

Reputation: 1908

Here the formula, in B10:

= arrayformula(
    { transpose
      ( split(join(rept(",",6),
        filter('Squad Compositions'!B4:B,'Squad Compositions'!B4:B<>"")) 
        ,",",true,false)
      ),(
      { transpose(split(substitute(textjoin( "%",false,
        { filter ('Squad Compositions'!E4:I,'Squad Compositions'!B4:B<>""),
          left(filter('Squad Compositions'!E4:E&"",'Squad Compositions'!B4:B<>""),0)
        } )& "$","%$",""),"%",true,false))
      })
    }
  )

Upvotes: 1

Related Questions