How does one concatenate strings that appear in blocks of rows using arrayformula?

CodePudding user response：

Here is a copy of your sheet. In it you'll find this formula which should work:

=ARRAYFORMULA(TRANSPOSE(TRIM(QUERY(MID(QUERY({TEXT(ROW(A2:A21),"000")&A2:A21,INT((ROW(A2:A21)-2)/5)},"select MAX(Col1) group by Col1 pivot Col2"),4,100),,9^9))))