How to change multiple columns into rows in Microsoft Excel

Question

My data is in three columns. I want each column to appear on the new line, without overwriting the existing line. Kindly help... Thanks

![How do I attain the Desired output? ][1]


  [1]: https://i.stack.imgur.com/pEGeG.png

My Excel Sheet

Original Lines      
<sentence>  AAA </sentence>
<sentence>  BBB </sentence>
<sentence>  CCC </sentence>

After Col Transpose     
<sentence>  <sentence>  <sentence>
AAA         BBB         CCC
</sentence> </sentence> </sentence>

Desired Output
    <sentence>
    AAA
    </sentence>
    <sentence>
    BBB
    </sentence>
    <sentence>
    CCC
    </sentence>

---

I have tried transpose but all appear in the same row. I want it to appear on separate row. Kindly Help Thanks.

![enter image description here][1]


  [1]: https://i.stack.imgur.com/aO3s3.png

Original Lines      
<sentence>  AAA </sentence>
<sentence>  BBB </sentence>
<sentence>  CCC </sentence>

After Col Transpose     
<sentence>  <sentence>  <sentence>
AAA BBB CCC
</sentence> </sentence> </sentence>

Desired Output
<sentence>
AAA
</sentence>
<sentence>
BBB
</sentence>
<sentence>
CCC
</sentence>

Answer 1

Have you tried using a formula? Put this in D1 and drag down:

=OFFSET($A$1,INT((ROW(A1)-1)/3),INT((((ROW(A1)-1)/3)- (INT((ROW(A1)-1)/3)))*3))

It works out an offset matrix using this for the row:

=INT((ROW(A1)-1)/3)

And this for the column:

=INT((((ROW(A1)-1)/3)- (INT((ROW(A1)-1)/3)))*3)

Then applies an offset from A1 using that matrix.