How to merge two columns into one column with each entry into a new row using macro in excel?

Question

I have data in excel in the following format:
|4 | 5|
|8 | 3|
|6 | 2|

and I want output in the form:
4
5
8
3
6
2
How this could be done using Macro in excel? Or can it be done using dataframe in R?

It can certainly be done in R; is there anything you've already tried yourself? — Heroka, Jan 06 '16 at 21:27
I used melt function but that gives first column and then the second column — kumar, Jan 06 '16 at 21:28
In R, [this](http://stackoverflow.com/questions/12044616/alternate-interweave-or-interlace-two-vectors) might be useful. You could try something like `c(t(as.matrix(dat)))`. — Heroka, Jan 06 '16 at 21:34
`data.frame(unlist(dat))`, or `stack(dat)` or `reshape2::melt(dat)` — ExperimenteR, Jan 07 '16 at 04:19

Gary's Student · Accepted Answer · 2016-01-06T21:35:39.250

0

With data in columns A and B, in another cell enter:

=OFFSET($A$1,ROUNDUP(ROWS($1:1)/2,0)-1,MOD(ROWS($1:1)-1,2))

and copy down:

NOTE:

Any rectangular block of data can be mapped into a single column with formulas similar to this.

edited Jan 06 '16 at 21:35

answered Jan 06 '16 at 21:33

Gary's Student

95,722
10
59
99

Simple and effective solution. Thanks – kumar Jan 06 '16 at 21:35

score 0 · Answer 2 · edited Jan 06 '16 at 23:52

If you are looking for a macro specifically, here is some quick VBA code to help:

Sub MergeColumns()
    'Declare some indexing variables
    Dim a, b, c, x As Integer

    ' x is where we want to put the column of merged numbers
    x = 3

    ' b is the index for which column to pickup
    ' start this at 1
    ' c is the row we wan to pick up, start it at 2
    b = 1
    c = 2

    ' Next loop through all the numbers on the left side
    For a = 2 To 7
        ' first set our first cell to the correct value
        Cells(a, x) = Cells(c, b)

        ' now we change the indexes
        If b = 1 Then
            ' change to the next colum
            b = 2
        Else
            ' otherwise b = 2 and we need to move rows
            b = 1
            c = c + 1
        End If
    Next a
End Sub

enter image description here

score 0 · Answer 3 · edited Jan 06 '16 at 23:53

Here is my answer, hope to help anyone.

Sub rangeToColumn()
    Dim rng As Range
    Dim i 'index of cells
    Dim f

    Set rng = Range("A1:B3")
    i = 0

    For Each f In rng
        i = i + 1
        Cells(i, 3).Value = f.Value
        'Cells({a}, {b}).Value = {c}
        '
        'a = the row you want to put the value, using the index will put the value in separated rows
        'b = you can use the column you want, or used hardcoded as  I did.
        'c = storing each cell into the c var, you will be able to take the values or any other property.
    Next f
End Sub

score 0 · Answer 4 · answered Jan 07 '16 at 00:05

This formula uses Index. Just for future reference, as it is similar to Gary's Students answer. But Indirect is volatile, as in it will recalculate if there is any change. The index formula will only reevaluate when the data it is referencing changes:

=INDEX($A$1:$B$3,QUOTIENT(ROW(1:1)-1,2)+1,MOD(ROW(1:1)+1,2)+1)

How to merge two columns into one column with each entry into a new row using macro in excel?

4 Answers4