Forum Discussion

Ingmar's avatar
Ingmar
Copper Contributor
Dec 07, 2018

Excel 2013 Row Transposing

Hello, I have an export in csv, which I need to redesign. Currently it is formatted like the example 1 on the left. But I'd like to convert it to format example 2. I'm kinda lost on how to accomplish this..? How would you solve this?

 

Kind Regards

excel
Excel Online
  • JWR1138's avatar
    JWR1138
    Iron Contributor
    Dec 13, 2018

    Hi, Both of these need to be entered as array formulas (Not sure if you have any familiarity there, enter the formula into the cell as below, hit CTRL+SHIFT+ENTER, Grab the handle and drag down or horizontally across other cells as needed)

     

    In Cell D2:

    =IFERROR(INDEX($A$2:$A$5,MATCH(0,COUNTIF($D$1:D1,$A$2:$A$5),0)),"")

     Drag Down as needed, for your example drag down to D3

     

     

    In Cell E2:

    =IFERROR(INDEX($B$2:$B$5,SMALL(IF($D2=$A$2:$A$5,ROW($A$2:$A$5)- MIN(ROW($A$2:$A$5))+1,""),COLUMN()-4)), "")

     Drag Right as needed (To F2 in your example) and then copy E2:F2 to E3:F3

     

    Sample workbook attached as well.

Resources