Forum Discussion

Jpey65's avatar
Jpey65
Copper Contributor
Mar 20, 2024
Solved

Maybe an array formula to transpose records?

I have a table of data. Note that the number of rows per index can vary and the total number of unique index values will vary and not be named TestX:

IndexConcat
Test1DataValue1
Test1DataValue2
Test1DataValue3
Test1DataValue4
Test1DataValue5
Test1DataValue6
Test1DataValue7
Test1DataValue8
Test1DataValue9
Test2DataValue10
Test2DataValue11
Test2DataValue12
Test2DataValue13
Test2DataValue14
Test2DataValue15
Test2DataValue16
Test2DataValue17
Test2DataValue18
Test2DataValue19
Test3DataValue20
Test3DataValue21
Test3DataValue22
Test3DataValue23
Test3DataValue24
Test3DataValue25
Test3DataValue26
Test3DataValue27
Test3DataValue28
Test3DataValue29

 

I would like to transpose the data so each index item shows the related data on one row:

IndexConcatNew Column1New Column2New Column3New Column4New Column5New Column6New Column7New Column8New Column9New Column10New Column11
Test1DataValue1DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue2DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue3DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue4DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue5DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue6DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue7DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue8DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue9DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test1DataValue10DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue11DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue12DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue13DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue14DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue15DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue16DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue17DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue18DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue19DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test2DataValue20DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test3DataValue21DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue22DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue23DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue24DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue25DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue26DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue27DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue28DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue29DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 
Test3DataValue30DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 

 

Ultimately, I would like to filter it down to unique indexes, but I can do that myself:

IndexConcatNew Column1New Column2New Column3New Column4New Column5New Column6New Column7New Column8New Column9New Column10New Column11
Test1DataValue1DataValue1DataValue2DataValue3DataValue4DataValue5DataValue6DataValue7DataValue8DataValue9  
Test2DataValue11DataValue10DataValue11DataValue12DataValue13DataValue14DataValue15DataValue16DataValue17DataValue18DataValue19DataValue20
Test3DataValue21DataValue21DataValue22DataValue23DataValue24DataValue25DataValue26DataValue27DataValue28DataValue29DataValue30 

 

My question is can I do at least the column to row (second step/picture) conversion with a formula? 

 

Thanks for any help you can provide.

excel
Formulas and Functions
office 365
  • Jpey65's avatar
    Jpey65
    Mar 26, 2024
    https://www.ablebits.com/office-addins-blog/textjoin-function-excel-merge-text-multiple-cells/#:~:text=The%20syntax%20of%20the%20TEXTJOIN,text%20value%20that%20you%20combine.

    =TEXTJOIN(", ", TRUE, IF([Index]=[@Index], [Concat], ""))


7 Replies

    • Jpey65's avatar
      Jpey65
      Copper Contributor
      Mar 26, 2024
      https://www.ablebits.com/office-addins-blog/textjoin-function-excel-merge-text-multiple-cells/#:~:text=The%20syntax%20of%20the%20TEXTJOIN,text%20value%20that%20you%20combine.

      =TEXTJOIN(", ", TRUE, IF([Index]=[@Index], [Concat], ""))


  • OliverScheurich's avatar
    OliverScheurich
    Gold Contributor
    Mar 20, 2024

    Jpey65 

    =LET(ind,A2:A34,

    conc,B2:B34,

    HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

     

    Does this return the intended result?

     

    • Jpey65's avatar
      Jpey65
      Copper Contributor
      Mar 25, 2024

      OliverScheurich it does not work. It returns 0. As mentioned above the data is in a table, so I modified the formula: 

      =LET(ind,A2:A34,conc,B2:B34,HSTACK(UNIQUE(ind),IFNA(DROP(REDUCE("",UNIQUE(ind),LAMBDA(u,v,VSTACK(u,TOROW(FILTER(conc,ind=v))))),1),"")))

       

      • OliverScheurich's avatar
        OliverScheurich
        Gold Contributor
        Mar 25, 2024

        Jpey65 

        =LET(ind;Tabelle4[Index];
        conc;Tabelle4[Concat];
        HSTACK(UNIQUE(ind);IFNA(DROP(REDUCE("";UNIQUE(ind);LAMBDA(u;v;VSTACK(u;TOROW(FILTER(conc;ind=v)))));1);"")))

         

        If you have a dynamic table you can apply this formula. In my example the name of the table is Tabelle4. In your worksheet you have to replace Tabelle4 with the name of your table.

Resources