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

 

[Fuji]

Assuming data starts from A1:Px.

Sub test()
    Dim x As String, y As String, myArray
    With [a1].CurrentRegion
        x = Range("a1").Resize(, .Count).Address
        y = .Columns.Count
        myArray = Application.Index(.Value, Evaluate("index(roundup(column(" & x & ")/" & y & ",0),,)"), _
                Evaluate("if(mod(column(" & x & ")," & y & ")=0," & y & ",mod(column(" & x & ")," & y & "))"))
    End With
    Sheets.Add.[a1].Resize(, UBound(myArray)).Value = myArray
End Sub

 

[JEC]

Or:

Sub jec()
 Dim ar, j As Long, jj As Long, x As Long
 ar = Cells(1).CurrentRegion
 ReDim ary(UBound(ar) * UBound(ar, 2))
 
 For j = 1 To UBound(ar, 2)
   For jj = 1 To UBound(ar)
     ary(x) = ar(jj, j)
     x = x + 1
   Next
 Next
 
 Sheets.Add.Cells(1).Resize(, x) = ary
End Sub

 

[albatros8]

Assuming data starts from A1:Px.

Sub test()
    Dim x As String, y As String, myArray
    With [a1].CurrentRegion
        x = Range("a1").Resize(, .Count).Address
        y = .Columns.Count
        myArray = Application.Index(.Value, Evaluate("index(roundup(column(" & x & ")/" & y & ",0),,)"), _
                Evaluate("if(mod(column(" & x & ")," & y & ")=0," & y & ",mod(column(" & x & ")," & y & "))"))
    End With
    Sheets.Add.[a1].Resize(, UBound(myArray)).Value = myArray
End Sub

I'm still testing, but I think this will work.
(I had/have to learn how to use Macros though. I'm not an expert )

 

[Fluff]

Welcome to the MrExcel Message Board!

Cross-posting (posting the same question in more than one forum) is not against our rules, but the method of doing so is covered by #13 of the Forum Rules.

Be sure to follow & read the link at the end of the rule too!

Cross posted at: Formula OR VBA: Excel multiple columns to rows
There is no need to repeat the link(s) provided above but if you have posted the question at other places, please provide links to those as well.

If you do cross-post in the future and also provide links, then there shouldn’t be a problem.