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

gingmac Source

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.

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. Thanks!

arraysgoogle-spreadsheetarray-formulasgoogle-sheets-query

Answers

answered 1 month ago gingmac #1

Solved by Lance over on Google forums.

comments powered by Disqus