CODEWITHSUNDEEP
vbaexcelexcel-formulaexcel-2010
shaadi
shaadi

Reputation: 171

how to combine cell vertically in excel

It might be the most silly question in planet. how can I merge two cell values vertically in a repeated manner. as like this:

enter image description here

Column A and B has 400+ cells therefore it is impossible to do what I want to achieve manually.

Note: I want to merge B into A.

Upvotes: 0

Views: 2389

Answers (2)

Michael
Michael

Reputation: 4858

You can create a simple loop in VBA that runs through each cell in the data range then adds it to the output column

Sub Merge()

Dim data As Range
Dim cell As Range
Dim output As Range
Dim i As Integer

Set data = Range("A2:B4")
Set output = Range("D2")
i = 0

For Each cell In data
    output.Offset(i, 0) = cell
    i = i + 1
Next

End Sub

Upvotes: 3

Michael
Michael

Reputation: 4858

You can use the INDEX function to refer to each cell. If data is in A2:B4, this formula works in any column but must start in row 2 and can then be filled down:

=INDEX($A$2:$B$4,ROW()/2,MOD(ROW(),2)+1)

The formula uses the current row as a counter. On every even row it gets a value from the first column of data and on every odd row it gets a value from the second column of data. After every 2 rows it gets values from the next row of data.

Upvotes: 0

Related Questions