Forum Discussion
Transpose in arrays of n columns
Hello all and thanks in adhance for your help. I am tryting to find a formula or shortcut that would allow me to transpose arrays of n columns into matrixes of n rows and n columns, to plot the d...
Another potential option, using TRANSPOSE and PIVOTBY, plus a few extra steps to sort the results and rearrange the header rows:
=LET(
arr, TRANSPOSE(A1:G5),
pvt, DROP(PIVOTBY(SCAN("",TAKE(arr,,1),LAMBDA(a,v,IF(v="",a,v))),INDEX(arr,,2),DROP(arr,,2),SUM,3,0,,0,-1),1),
srt, SORTBY(pvt,INDEX(pvt,2)),
grp, INDEX(srt,2),
VSTACK(IF(grp<>HSTACK("",DROP(grp,,-1)),"Group "&grp,""),TAKE(srt,1),DROP(srt,2))
)Note: the SCAN function used in the row_fields argument of PIVOTBY assumes the "Grade" headers shown in your raw data screenshot have Merge & Center formatting applied (they appear to, considering the vertical gridlines are not visible).
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!
