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 colum...
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.

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

 

 

mathetes's avatar
mathetes
Gold Contributor
Apr 01, 2023

NikeMike 

 

I've not actually done this before, so I'm going to have to do some "playing" with it. I know there are some newer functions that can help. In the meantime, if you enjoy researching and experimenting, this link might get you started on solving it yourself.

Resources