Forum Discussion
In Excel, how do you rearrange a specific range of columns (of a given row) into separated rows?
The transpose function converts an entire row into one single column. This is not my goal. I want to reallocate a fixed range of columns (within a given row) into rows. For instance, suppose I have the table below:
| 0 | 1 | 2 | ||||||
| 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9 |
| 22 | 12 | 0 | 9 | 0 | 65 | 6666 | 55 | 3 |
Using excel, how can I reorganize the second row into a new table such that only the second row is used, and each new row of the new column corresponds to a range of 3 values from the original table? Something in which the outcome would be:
| 1 | 2 | 3 |
| 4 | 5 | 6 |
| 7 | 8 | 9 |
Thank you for your time !
If you have the Current channel of O365, you should be able to use WRAPROWS:
If not, you could try something like this:
Note that the third parameter of INDEX represents the column indices in the row you are trying to reshape. So, regardless of the values in that row, if you pass the index-structure into that parameter, it will wrap the row as required.
Note that in my culture, comma is the array column separator while semi-colon is the array row-separator.
5 Replies
Also could be a combination of index and sequence.
=INDEX(A1:I3,B5,SEQUENCE(3,3))
Attached is an example.
If you have the Current channel of O365, you should be able to use WRAPROWS:
If not, you could try something like this:
Note that the third parameter of INDEX represents the column indices in the row you are trying to reshape. So, regardless of the values in that row, if you pass the index-structure into that parameter, it will wrap the row as required.
Note that in my culture, comma is the array column separator while semi-colon is the array row-separator.
- Thank you so much, your solution is sublime!
- Thank you for your help ^^
