Forum Discussion
Automate transpose every 5 rows into 5 columns
Hello,
I want to transpose every 5 rows into 5 columns
From this :
into this :
Is there any way to do it in one go ?
Thank you!
Let's say the data are on a worksheet named Sheet 1.
On another sheet in A1:
=LET(t, TRANSPOSE(INDEX('Sheet 1'!A:A, 5*ROW()-4):INDEX('Sheet 1'!A:A, 5*ROW())), IF(t="", "", t))
Fill down as far as needed/wanted.
=DROP(WRAPROWS(A1:A25,5),,-1)
With Office 365 and Excel for the web this formula could be an alternative. With legacy Excel e.g. Excel 2013 an alternative could be Power Query.
- How do it with Power Query?
The attached sample file uses Power Query. In the attached file you can add data to the blue dynamic 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.
The data layout in the attached file is for illustration. You can place the green result table in another worksheet as well.
