CODEWITHSUNDEEP
excel
Habib Zare
Habib Zare

Reputation: 1204

How to add two columns data to one column in excel?

I have two columns in excel like below :

enter image description here

I want add A and B to C. the result is :

enter image description here

how can I do (formula)?

and when i add data to any list(A or B) it automatically adds to column C.

Upvotes: 1

Views: 2873

Answers (3)

TechnoCrat
TechnoCrat

Reputation: 708

you can do it without macro using below formula into C1 cell and then drag it till C8.

=IF(ROW()<=COUNTA(A:A),INDEX(A:A,ROW()),IF(ROW()<=COUNTA(A:B),INDEX(B:B,ROW()-COUNTA(A:A)),IF(ROW()>COUNTA(A:C),"",INDEX(C:C,ROW()-COUNTA(A:B)))))

Upvotes: 1

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96753

In C1 enter:

=IF(ROW()<=COUNTA(A:A),A1,INDEX(B:B,ROW()-COUNTA(A:A)))

and copy down

enter image description here

Upvotes: 1

adrCoder
adrCoder

Reputation: 3275

If I understand correctly you want to append one column after the other...

That you can do it using Excel VBA code :

Press ALT + F11 to open the Visual Basic Editor, Insert > Module and paste into the white space on the right

Code:

Sub Append()
Dim LR As Long, LC As Integer, j As Integer
LC = Cells(1, Columns.Count).End(xlToLeft).Column
For j = 2 To LC
    LR = Cells(Rows.Count, j).End(xlUp).Row
    Range(Cells(1, j), Cells(LR, j)).Copy Destination:=Cells(Rows.Count, 1).End(xlUp).Offset(1)
Next j
End Sub

Press ALT + Q to return to your sheet, Tools > Macro > Macros, click on Append then click the Run button.

Upvotes: 0

Related Questions