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.
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.
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
Well, here's a "quick and dirty" solution. I'm sure it could be more elegant, but I'm also assuming this is a job that just needs to be done once.
I've attached an Excel sheet that shows the process getting started. Using the CHOOSEROWS function, and here's a reference on that function.
=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.