Forum Discussion
i3erriiz
Apr 01, 2021Copper Contributor
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
Apr 03, 2021Diamond Contributor
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)
)
)