excel - How can i copy columns to one column

Question

Hope someone can help in coping more than 500 columns from excel to notepad in one column .. when i used the usual coping it did but beside each other. i need to copy them in one column .. i tried to search for a solution and found this forumla

=IFERROR(INDEX($A$1:$D$500,MOD(AGGREGATE(15,6,COLUMN($A$1:$SF$500)*10^6+ROW($A$1:$SF$500)/(($A$1:$SF$500<>"")),ROWS(SG$1:SG1)),10^6)-ROW($A$1)+1,AGGREGATE(15,6,COLUMN($A$1:$SF$500)/(($A$1:$SF$500<>"")),ROWS(SG$1:SG1))-COLUMN($A$1)+1),"")

but without any progress .. I'm stuck in this problems really . if there is another tool or formula i can use it to do that . I'll appreciate it .. thanks

question from:https://stackoverflow.com/questions/65908265/how-can-i-copy-columns-to-one-column

Answer 1

Try this: https://www.extendoffice.com/documents/excel/4233-excel-stack-columns.html.

=INDEX(MyData,1+INT((ROW(A1)-1)/COLUMNS(MyData)),MOD(ROW(A1)-1+COLUMNS(MyData),COLUMNS(MyData))+1)

, where mydata is the range you have to insert in the column.

If you don't want to use the formula, try vba:

Function ToColumn(rng As range) As Variant
    Dim arr
    Dim cel As range
    Dim str As String

    For Each cel In rng
        If Len(cel) > 0 Then
            If str = "" Then
                str = cel
            Else
                str = str & "," & cel
            End If
        End If
    Next cel
    arr = Split(str, ",")
    ToColumn = Application.Transpose(arr)
End Function

How to use this function:

First select the range (example F1:F5) you think will fit your data, then select your range (A1:D2) you want to import in your column and type =IFERROR(ToColumn(D1:CY500),"") and press Ctrl+Shift+Enter.