Forum Discussion

heyarray's avatar
heyarray
Copper Contributor
Feb 20, 2025

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?    
excel
Formulas and Functions
djclements's avatar
djclements
Silver Contributor
Feb 22, 2025

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!

Resources