Forum Discussion
Shaping Dynamic Arrays: Moving beyond Transpose
If you have a triangular table of a certain shape, can it be reshaped so that rows going across become columns going down but with padding at the beginning?
4 Replies
= LET( extended, EXPAND(data,, 12,""), DROP(WRAPCOLS(TOCOL(extended), 11),-5,-1) )This lining up of a matrix reverse diagonal is characteristic of convolutions and depreciation calculation. Array shaping is not just decoration, it can be a key element of the calculation.
Nice one Peter! Very clever. ;) Naturally, it could be made dynamic by defining n, COLUMNS(data), then replacing 12 with n*2, 11 with n*2-1, and -5 with 1-n, but it's perfect for the described scenario, nonetheless. The only potential drawback I see is limitations... since it's expanding the width of the array, TOCOL will only handle 724x724 (expanded to 724x1448) as opposed to 1024x1024, but that's likely a non-issue. Cheers!
There are a number of new array reshaping functions, such as TOCOL, TOROW, WRAPCOLS and WRAPROWS, which can be used with SORTBY and SEQUENCE for this scenario:
=LET(arr,A2:C4,WRAPCOLS(SORTBY(TOCOL(IF(arr="","",arr)),TOCOL(IF({1},SEQUENCE(ROWS(arr)),arr)),1,TOCOL(IF(arr="",0,SEQUENCE(1,COLUMNS(arr)))),1),COLUMNS(arr)))Or, as a custom LAMBDA function with an optional [scan_by_col] argument:
=LAMBDA(arr,[scan_by_col], IF( scan_by_col, WRAPROWS(SORTBY(TOCOL(IF(arr="","",arr)),TOCOL(IF({1},SEQUENCE(1,COLUMNS(arr)),arr)),1,TOCOL(IF(arr="",0,SEQUENCE(ROWS(arr)))),1),ROWS(arr)), WRAPCOLS(SORTBY(TOCOL(IF(arr="","",arr)),TOCOL(IF({1},SEQUENCE(ROWS(arr)),arr)),1,TOCOL(IF(arr="",0,SEQUENCE(1,COLUMNS(arr)))),1),COLUMNS(arr)) ) )(A2:C4,1)Cheers!
=TRANSPOSE(LET(rng,A1:G6,DROP(IFNA(REDUCE("",SEQUENCE(ROWS(rng)), LAMBDA(u,v,VSTACK(u,HSTACK(EXPAND("",,v),TOROW(CHOOSEROWS(rng,v),1))))),""),1,1)))With Office 365 or Excel for the web this formula returns the intended result.
