Split rows dynamically based on row length (COUNTA) and transpose to 2D array

Question

I have data in this format:

And I need it to be reformatted into this:

Basically, each row in the initial format is composed of 4 concatenated sections of equal lengths: Part(s), Product(s), Quant(s), and Employee(s). If a row in the initial format has a COUNTA value of 12, the desired reformatted array will split that row into 3 rows. Rows in the initial format could get long (possibly 200+ columns wide?), but they will always be divisible by 4.

Here's an example sheet: spreadsheet sample.

I'm banging my head against a wall to figure this out. I'm usually pretty fluent in Google Sheets, but I have no experience with scripts or RE2, etc... I'd love to get this all packed into an array formula using something like:

=ARRAYFORMULA(TRANSPOSE(REPT(OFFSET(A2:KL7,,,1,1),COUNTA(A2:KL7)/4)))

But it obviously isn't cutting it. For starters, I can't figure out how to make the Offset loop and move over a column each time...

Like I said, I'd like to avoid scripts just so I can understand the formula and so that it will run automatically for constant reformatting.

Would really appreciate any help out there.

Answer 1

Solved by Lance over on Google forums.

=ARRAYFORMULA(RegexReplace({
    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##1"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##2"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##3"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))\

    Transpose(Split(TextJoin("@";1;IF(RegexMatch(IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);"^##4"); IF(A2:AZ7="";;"##"&RoundUP(Column(A2:AZ2)/(MMult(IF(Len(A2:AZ7);1;0);Transpose(Sign(Column(A2:AZ2))))/4))&" "&A2:AZ7);));"@";1;1))

};"##\d\s";))