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.

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

     

     

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

      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.

    • OliverScheurich's avatar
      OliverScheurich
      Gold Contributor
      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 03, 2023

        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

    • mathetes's avatar
      mathetes
      Silver 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