Forum Discussion
Unstack data from a double column
=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.
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
=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.
Hi OliverScheurich
Im trying to follow how you did that - after I added the helper column, how do I get to the second table?
You file works nicely, however, I d like to understand how you did that (if you'd be so nice to explain me).
Thanks, NIf you don't already have the blue dynamic table then select a cell in the range and create the dynamic table with ctrl+T.
Then you can start the Power Query Editor like shown in the screenshot.
Now you see the Power Query Editor where you can pivot the data. Select "Transformieren" (highlighted in yellow) and pivot column (highlighted in yellow with a blue cirlce). In the small screen you can select the options as indicated in the screenshot. Select "nicht aggregieren" which means do not aggregate. Then click ok.
Below is the pivoted table. You can select the first column and remove it.
Then you can select the column with the time and change the format to time.
EDIT: Select the column with the date and change the format to date ("Datum" in german Excel). Then screenshot shows "Zeit" and "time" by mistake.
Then close and load the result.
Then load only connection. Then you can decide where you want to place the green result table.
Then follow the steps in the screenshot to load the result table ("Laden in" in the screenshot means load into).
Then enter the range where you want to enter the result table. "Bestehendes Arbeitsblatt" means this worksheet.
- Thanks a lot! Thats so much effort you made there!
I didnt know that I would have to choose dont aggregate in pivot, thats the point where I failed.
Again, thanks so much!