Excel multiple columns to rows

[albatros8]

Dear,

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):

1,110	1,090	1,090	1,089	1,116	1,107	1,095	1,090	1,092	1,090	1,127	1,097	1,086	1,103	1,092	1,109
1,118	1,097	1,083	1,084	1,090	1,119	1,100	1,084	1,090	1,087	1,109	1,087	1,084	1,086	1,090	1,090
1,129	1,092	1,078	1,078	1,077	1,116	1,089	1,086	1,086	1,089	1,089	1,106	1,081	1,081	1,095	1,090

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

1,110

1,090

1,090

1,089

1,116

1,107

1,095

1,090

1,092

1,090

1,127

1,097

1,086

1,103

1,092

1,109

1,118

1,097

1,083

1,084

1,090

1,119

1,100

1,084

1,090

1,087

1,109

1,087

1,084

1,086

1,090

1,090

1,129

1,092

1,078

1,078

1,077

1,116

1,089

1,086

1,086

1,089

1,089

1,106

1,081

1,081

1,095

1,090

Any help??

[#2]

@albatros8 

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.

[#3]

@albatros8 

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.

[#4]

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.....
=INDEX($A$1:$P$3,INT((COLUMN()-1)/16)+1,MOD(COLUMN()-1,16)+1)

[#5]

@OliverScheurich 

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

🙂

[#6]

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...

[#7]

@albatros8 

It should work in Excel 2013.

What do you see if you open the attached workbook?

[#8]

Hay.
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.

[#9]

@albatros8 

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

= TOROW(array)

shows why.

[#10]

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

[#11]

@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.

[#12]

@rachel 

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!

[#13]

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.