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:
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.