Trying to transpose three columns together, five rows at a time in GoogleSheets

I have a GoogleSheet with three columns of data spanning 53 rows. I need to transpose those 53 rows across 15 columns but do so five rows at a time. So I need a formula that will take columns A-C, rows 2-5 and put that data in columns D-O, row 1 and then begin again with columns A-C rows 7-10 and put that data in columns D-O, row 2, etc.

I'm not a script or formula-savvy individual; I've had success in the past using ChatGPT to write both for me, but every recommended formula or script it has given me for this results in a variety of errors, mainly: Array result was not expanded because it would overwrite data in E1.

Any help with a formula would be so very much appreciated.

Here are just a few of the formulas I've tried using at the suggestion of ChatGPT:

=IF(COLUMN(A1:C1) <= 3, INDEX($A:$C, ROW(A1)*5-5+COLUMN(A$1:C$1)), "")

=IF(COLUMN(A1:C1) <= 3, INDEX($A:$C, ROW(A1)*5-5+COLUMN(A1)), "")

=ArrayFormula(INDEX($A:$C, (ROW(A1:A)-1)*5+COLUMN(A1:C)))

=INDEX($A:$C, ROW(A1)*5-5+COLUMN(A1)-COLUMN($A:$A))

=ARRAYFORMULA(TRANSPOSE(INDEX(A:A, (ROW()-1)*5+1):INDEX(O:O, (ROW()-1)*5+5)))

=ARRAYFORMULA(TRANSPOSE(OFFSET($A$1, (ROW()-1)*5, 0, 1, 15)))

=ARRAYFORMULA(INDEX(Sheet1!A:C, ((ROW(A1:A)-1)*5)+1, MOD(SEQUENCE(1,15,0,1),3)+1))

=ARRAYFORMULA(INDEX(Sheet1!A:C, ((ROW(D2:D)-2)*5)+1, MOD(SEQUENCE(1,15,0,1),3)+1))

=ARRAYFORMULA(INDEX(Sheet1!$A$2:$C, ((ROW(D2:D)-2)*5)+1, MOD(SEQUENCE(1,15,0,1),3)+1))

=ARRAYFORMULA(OFFSET(Sheet1!$A$1:$C$1, (ROW(Sheet1!A1:A)-2)*5, 0))

=ARRAYFORMULA({SPLIT(FLATTEN(TRANSPOSE(SPLIT(TEXTJOIN(",", TRUE, OFFSET(Sheet1!A1:C, (ROW(Sheet1!A1:A)-1)*5, 0, 5)), ","))), ","),SPLIT(FLATTEN(TRANSPOSE(SPLIT(TEXTJOIN(",", TRUE, OFFSET(Sheet1!D1:F, (ROW(Sheet1!A1:A)-1)*5, 0, 5)), ","))), ","), SPLIT(FLATTEN(TRANSPOSE(SPLIT(TEXTJOIN(",", TRUE, OFFSET(Sheet1!G1:I, (ROW(Sheet1!A1:A)-1)*5, 0, 5)), ","))), ",")})

1 Answer

You can use:

=IFNA(WRAPROWS(TOROW(Sheet1!A2:C),15))

Where 15 is 3*5 which is the number of columns in the range times the amount of columns per row.

For the header, you can use:

=ARRAYFORMULA(TOROW(IF(SEQUENCE(5),Sheet1!A1:C1)))