Forum Discussion
Excel multiple columns to rows
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??
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 SubIn 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.
- 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...
- 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) 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….
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!
