0

I have data in this format:

screenshot of data to be reformatted

And I need it to be reformatted into this:

reforatted data

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.

0

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";))

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.