Forum Discussion

i3erriiz's avatar
i3erriiz
Copper Contributor
Apr 01, 2021

Excel : Transpose group of data use formular

I want to make this  R.M. 1 6683-19-8       R.M. 2 512-56-1       R.M. 3 51541-08-3       R.M. 4 25750-84-9       R.M. 5 471-34-1       R.M. 6 9002-84-0 25852...
SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Apr 03, 2021

i3erriiz 

For such model

formula could be

=LET(
     cols, COLUMNS(Table1),
     rws,  ROWS(Table1),
     k,    SEQUENCE((cols-1)*rws),
     row,  INT((k-1)/(cols-1))+1,
     col,  MOD(k-1,cols-1)+2,
     data, INDEX(Table1,row,col),
     name, INDEX(Table1,row,1),
     IF({1,0},
        FILTER(name,data<>0),
        FILTER(data,data<>0)
     )
)