Unstack data from a double column

[NikeMike]

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

[#2]

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

[#3]

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

 

[#4]

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

[#5 · Accepted solution]

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

[#6]

@NikeMike 

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.

[#7]

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;

 

[#8]

Hi 
Thanks @mathetes, @peiyezhu and @OliverScheurich. for your help and solutions. This helped me alot sorting the original file with approx 12k rows.
I really appreciate your inputs and help.
Best, M

[#9]

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, N

[#10]

@NikeMike 

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

[#11]

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!

[#12]

if possible,try copy data and get result on this online tool http://e.anyoupin.cn/EData/?s=multi_row