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

=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))," ") 2337 1627 2044 1018 1841 1841 1421 1057 2071 1627 1677 1018 1734 1734 1066 1018 1945 1615 1677 1018 1637 1637 1066 1018 1627 1627 1615 2044 1677 1677 1018 1018 1018 1841 1734 1637 1841 1734 1637 1421 1066 1066 1057 1018 1018 <colgroup><col width="64" span="8" style="width:48pt"> </colgroup><tbody> </tbody>

<tbody>
</tbody>