Excel multiple columns to rows

Copper Contributor

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,1101,0901,0901,0891,1161,1071,0951,0901,0921,0901,1271,0971,0861,1031,0921,109
1,1181,0971,0831,0841,0901,1191,1001,0841,0901,0871,1091,0871,0841,0861,0901,090
1,1291,0921,0781,0781,0771,1161,0891,0861,0861,0891,0891,1061,0811,0811,0951,090


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

1,1101,0901,0901,0891,1161,1071,0951,0901,0921,0901,1271,0971,0861,1031,0921,1091,1181,0971,0831,0841,0901,1191,1001,0841,0901,0871,1091,0871,0841,0861,0901,0901,1291,0921,0781,0781,0771,1161,0891,0861,0861,0891,0891,1061,0811,0811,0951,090


Any help??

12 Replies

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

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

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)

@OliverScheurich 

 

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

@albatros8 

It should work in Excel 2013.

What do you see if you open the attached workbook?

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.

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

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.

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