Forum Discussion
Transpose in arrays of n columns
We have a really interesting set of alternatives here, each with pros and cons.
David. I didn't understand how you got PIVOTBY to work without much more in the way of preliminary work normalising the crosstab, so I added code to step through your solution. I hadn't realised that PIVOTBY would work with multiple fields.
If anything I think your solution is probably the best but it was not a one horse race! The image below is my 'test environment' (if only it were quicker to set up).
My apologies for the minor code changes.
No worries, Peter. And thank you for providing the step-by-step walkthrough. "Best" is very much a subjective term in this game, so I'm not too worried about that. ;) One of my primary goals over the past year and a half or more has been to discover and share efficient alternatives to the generic DROP-REDUCE-STACK method. GROUPBY and PIVOTBY have been a game changer in that regards, eliminating the need for complex intermediary steps in many cases. I had previously used GROUPBY with multiple value fields, but this was my first time trying it with PIVOTBY. It worked out well!