how to move data under a columns under 1 ray

[mydreamnet]

Dear Sir
I need to move column under one column that fast way I can with our cut past because I have huge date
In the picture below you can see the sample

 

[Peter_SSs]

Re: how to move data under a columns under 1 ray ????

Welcome to the MrExcel board!

For data set out like in your first image, try this on a copy of the data.

Sub MoveCols()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  With Range("E4").CurrentRegion
    a = .Value
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 1)
    For j = 1 To UBound(a, 2)
      For i = 1 To UBound(a, 1)
        k = k + 1
        b(k, 1) = a(i, j)
      Next i
    Next j
    .ClearContents
    .Resize(UBound(b), 1).Value = b
  End With
End Sub

 

[mydreamnet]

Re: how to move data under a columns under 1 ray ????

Dear Sir
should I make a Macro or what should I do

Welcome to the MrExcel board!

For data set out like in your first image, try this on a copy of the data.

Sub MoveCols()
  Dim a As Variant, b As Variant
  Dim i As Long, j As Long, k As Long
  
  With Range("E4").CurrentRegion
    a = .Value
    ReDim b(1 To UBound(a, 1) * UBound(a, 2), 1 To 1)
    For j = 1 To UBound(a, 2)
      For i = 1 To UBound(a, 1)
        k = k + 1
        b(k, 1) = a(i, j)
      Next i
    Next j
    .ClearContents
    .Resize(UBound(b), 1).Value = b
  End With
End Sub

 

[mydreamnet]

Re: how to move data under a columns under 1 ray ????

Dear Sir
Many thanks it worked perfectly

 

[Peter_SSs]

Re: how to move data under a columns under 1 ray ????

Dear Sir
Many thanks it worked perfectly

You are very welcome. Glad you got it working.

 

[Mike Szczesny]

Re: how to move data under a columns under 1 ray ????

Can you something like this? This is a crazy complicated, yet robust formula. You need to use Cntrl+Shift+Enter. Then copy down. I also inclosed an =ifferror(....." ") to remove error messages. Here is my formula [TABLE="width: 64"]
<tbody>[TR]
[TD="width: 64"] =IFERROR(INDEX($B$1:$H$3,MOD(SMALL(IF($B$1:$H$3<>"",(COLUMN($B$1:$H$3)-COLUMN($B$1)+1)*10^9+ROW($B$1:$H$3)-ROW($B$1)+1),ROWS($A$4:A4)),10^9),INT(SMALL(IF($B$1:$H$3<>"",(COLUMN($B$1:$H$3)-COLUMN($B$1)+1)*10^9+ROW($B$1:$H$3)-ROW($B$1)+1),ROWS($A$4:A4))/10^9))," ")

[TABLE="width: 512"]
<colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]2337[/TD]
[TD="width: 64, align: right"]1627[/TD]
[TD="width: 64, align: right"]2044[/TD]
[TD="width: 64, align: right"]1018[/TD]
[TD="width: 64, align: right"]1841[/TD]
[TD="width: 64, align: right"]1841[/TD]
[TD="width: 64, align: right"]1421[/TD]
[TD="width: 64, align: right"]1057[/TD]
[/TR]
[TR]
[TD="align: right"]2071[/TD]
[TD="align: right"]1627[/TD]
[TD="align: right"]1677[/TD]
[TD="align: right"]1018[/TD]
[TD="align: right"]1734[/TD]
[TD="align: right"]1734[/TD]
[TD="align: right"]1066[/TD]
[TD="align: right"]1018[/TD]
[/TR]
[TR]
[TD="align: right"]1945[/TD]
[TD="align: right"]1615[/TD]
[TD="align: right"]1677[/TD]
[TD="align: right"]1018[/TD]
[TD="align: right"]1637[/TD]
[TD="align: right"]1637[/TD]
[TD="align: right"]1066[/TD]
[TD="align: right"]1018[/TD]
[/TR]
[TR]
[TD="align: right"]1627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1627[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1615[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]2044[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1677[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1677[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1841[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1734[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1637[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1841[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1734[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1637[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1421[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1066[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1066[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1057[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]1018[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]