Forum Discussion

NikeMike's avatar
NikeMike
Copper Contributor
Apr 01, 2023
Solved

Unstack data from a double column

Hi, 

I have an excelsheet with two columns of data. In the first Column there are the units "Name", "Date", "Code", "Storage", "Origin" and sometimes "Descrption" and or "Place". In the second column, there is the corresponding data. Now I would like to unstack the two columns so that I have the units in one row and all single datasets one per row underneath them. Any chance some kniws how to do that with excel?

 

thanks and best regards, M

excel
  • OliverScheurich's avatar
    OliverScheurich
    Apr 01, 2023

    NikeMike 

    =COUNTIF($A$2:A2,"Name und Autor")

    An alternative could be Power Query along with a helper column with this formula. In the attached file you can add data to the blue dynamic table. Then fill the formula down from cell C2 to the end of the table. Then you can click in any cell of the green table and right-click with the mouse and select refresh to update the green result table.

11 Replies

  • peiyezhu's avatar
    peiyezhu
    Bronze Contributor
    Apr 01, 2023

    if with sql.

    select (rowid-1)/5+1 row,group_concat(f02,'</td><td>') detail from multi_rows_to_one_row group by (rowid-1)/5;

    select (rowid-1)/5+1  row,group_concat(f02,'</td><td>') detail  from multi_rows_to_one_row group by (rowid-1)/5;

     

     

  • mathetes's avatar
    mathetes
    Silver Contributor
    Apr 01, 2023

    NikeMike 

     

    It might be done with Copy....Paste Special...Transpose, or with one or two functions that can accomplish the same thing. It would be a LOT easier, though, to help you if you could post a copy of your spreadsheet (or a mockup if the actual data are confidential), posting it on OneDrive or GoogleDrive with a link pasted here that grants edit access.

    • NikeMike's avatar
      NikeMike
      Copper Contributor
      Apr 01, 2023

      Hi mathetes 

      Of course, i also added a screenshot below. I have only put the first 300 lines in the file.

      In the picture row 2-6 are one dataset - I would like to have the values of each dataset in one row instead of one column. To make it a bit more complicated, some of the datasets have 1-3 additional statements.

      Thanks & Best, M

      https://1drv.ms/x/s!AtkdbFqXXz0Ugymk-S7YsZe59_7S?e=tlb0xJ

       

       

Resources