Forum Discussion

i3erriiz's avatar
i3erriiz
Copper Contributor
Apr 02, 2021

Excel : Transpose group of data use formular

I want to make this 

R.M. 16683-19-8   
R.M. 2512-56-1   
R.M. 351541-08-3   
R.M. 425750-84-9   
R.M. 5471-34-1   
R.M. 69002-84-025852-37-3  
R.M. 7110-30-5   
R.M. 865997-17-3   
R.M. 91309-64-4   
R.M. 1071342-77-3   
R.M. 1126062-94-268308-22-51333-86-473138-44-0
R.M. 1226062-94-2  

 

 

Into this so later i use for calculation what formular to use?

R.M. 16683-19-8
R.M. 2512-56-1
R.M. 351541-08-3
R.M. 425750-84-9
R.M. 5471-34-1
R.M. 69002-84-0
R.M. 625852-37-3
R.M. 7110-30-5
R.M. 865997-17-3
R.M. 91309-64-4
R.M. 1071342-77-3
R.M. 1126062-94-2
R.M. 1168308-22-5
R.M. 111333-86-4
R.M. 1173138-44-0
R.M. 1226062-94-2

1 Reply

  • 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)
     )
)

     

Resources