Excel multiple columns to rows

Copper Contributor


I would kindly ask for help.
I'm using Excel 2013.

I have 16 columns and big numbers of rows (e.g. 300+)

I would like that each row, would be placed next to the ending (16th) column.

Example (only for 3 rows):


And I would like the result in one row only, e.g.:


Any help??

12 Replies


Sub transform()

Dim i As Long, j As Long, k As Long, m As Long
Dim arr

i = Range("A" & Rows.Count).End(xlUp).Row
ReDim arr(1 To 1, 1 To i * 16)

k = 1
For m = 1 To i

For j = 1 To 16
arr(1, k) = Cells(m, j).Value
k = k + 1

Next j

Next m
Cells(i + 2, 1).Resize(1, i * 16).Value = arr

End Sub

In the attached sample file you can click the button in cell R2 to run the macro. The result is returned 2 rows below the database of 16 columns with many rows. This macro works in my Excel 2013 file.


Using a formula: on another sheet, enter the following formula in A1:

=INDEX('Sheet 1'!$A$1:P$300, QUOTIENT(COLUMN()-1, 16)+1, MOD(COLUMN()-1, 16)+1)

where Sheet 1 is the name of the sheet with the data.

Fill or copy to the right.


Another way would be a VBA macro. Let me know if you prefer that.

Formula version:
Assuming your data is in $A$1:$P$3.
Puttting below formula in ColumnA, drag it to the right as far as your need.....



Thanks. I believe this will work. So far, so good. 


This one is not working for me... I don't know why. I'm doing something wrong, or the versions (Excel 2013) doesn't have the same functions or signs, etc...


It should work in Excel 2013.

What do you see if you open the attached workbook?

Now it works. The signs were different. It must be ";" instead of ","

In this case, I need to expand the formula to get all the data.

Thank you.


For me the answer is "Do not use 2013 or, indeed, anything other than 365".  The simplicity of the formula

= TOROW(array)

shows why.

Funny most mathematicians or physicists I know love legacy, it is the IT people that prefer the more coding look alike 365….

@albatros8 If you use comma as decimal separator (two-and-a-half is written as 2,5), then you should use semicolon ; between the arguments of a function.

If you use point as decimal separator (two-and-a-half is written as 2.5), then you should use comma between the arguments of a function.

Most users here will assume that you use the latter unless you explicitly specify otherwise in your post.


No, I have maths, theoretical physics and astronomy degrees from way back, but my work was as an aeronautics research engineer.  The methods I applied were originally known as matrix methods so I may well have written more matrix formulae than English language sentences!    


I loath the concept of relative referencing, an array of scalar formulas is a poor substitute for a proper array formula.  I don't like the A1 notation either.  In a formula I want to know what the terms represent, not where they happen to be located on a worksheet.  Sure, it works well for others who want a hands-on experience and no fancy theory, but it's not for me!


There, that should get me drummed out of the Brownie spreadsheet circle!:lol:

Yes, 365 is more powerful. legacy is mainly to amuse myself. I see A1 as an alternative for (i, j) indexing for two dimension array. so I am reasonably ok with it. It is the 1 based indexing that annoys me. I prefer array index to start from 0.