Merge two colums in one one below other.

[sakis_s]

Hi!
How can i turn this (Columns A & B):

1	A
2	B
3	C

into this (Column C):

1

2

3

A

B

C

Thank you in advance for your help!

 

[DanteAmor]

Try this:

Sub mergeColumn()
  Dim lr As Long, lr2 As Long
  lr = Range("A" & Rows.Count).End(3).Row
  lr2 = Range("B" & Rows.Count).End(3).Row
  Range("C1").Resize(lr).Value = Range("A1:A" & lr).Value
  Range("C" & lr + 1).Resize(lr2).Value = Range("B1:B" & lr2).Value
End Sub

 

[kweaver]

Try this:

=OFFSET(DataToMerge,MOD(ROW()-ROW(ColD),ROWS(DataToMerge)),TRUNC((ROW()-ROW(ColD))/ROWS(DataToMerge),0),1,1)

Where DataToMerge is A1:B3 and ColD is C1:C6

 

[sandy666]

another approach with Power Query

let
    Source = Table.ToColumns(Excel.CurrentWorkbook(){[Name="Table1"]}[Content]),
    TFL = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    Exp = Table.ExpandListColumn(TFL, "Column1")
in
    Exp

Column1	Column2		Column1
1	A		1
2	B		2
3	C		3
			A
			B
			C

 

[Rick Rothstein]

Assuming you data will always start at Row 1, assuming Columns A and B will always have the same number of entries in them and assuming there will be no blanks cells within your data, you can put this formula in cell C1 and copy it down as far as you like and it will automatically adjust when you finish adding both values in the new row you place data in...

=IFERROR(INDEX(A:B,1+MOD((ROWS(C$1:C1)-1),COUNTA(A:A)),1+(INT(ROWS(C$1:C1))-1)/COUNTA(A:A)),"")

 

[sakis_s]

Thank you all for your responses, i really appreciate your help!

Answer by DanteAmor is the best in my case, thank you so much!

 

[DanteAmor]

Glad we could help & thanks for the feedback

 

[sakis_s]

Try this:

Sub mergeColumn()
  Dim lr As Long, lr2 As Long
  lr = Range("A" & Rows.Count).End(3).Row
  lr2 = Range("B" & Rows.Count).End(3).Row
  Range("C1").Resize(lr).Value = Range("A1:A" & lr).Value
  Range("C" & lr + 1).Resize(lr2).Value = Range("B1:B" & lr2).Value
End Sub

Hi again! I use this for joining two columns in one and it works perfectly.
Can anyone tell me what should i add to join 3 columns or more?
E.g. Joining columns A, B & C to column D.

Thank you very much for your help

 

[alansidman]

If you use Sandy666's solution it will work for as many columns as you have. There will be no need for any adjustment to the code.

Power Query is a free AddIn for Excel 2010 and 2013, and is built-in functionality from Excel 2016 onwards (where it is referred to as "Get & Transform Data").

It is a powerful yet simple way of getting, changing and using data from a broad variety of sources, creating steps which may be easily repeated and refreshed. I strongly recommend learning how to use Power Query - it's among the most powerful functionalities of Excel.

- Follow this link to learn how to install Power Query in Excel 2010 / 2013.

- Follow this link for an introduction to Power Query functionality.

- Follow this link for a video which demonstrates how to use Power Query code provided.

 

[DanteAmor]

Change "A:C" for the columns you want to join. The union will be in the next column.

Sub Merge_Columns()
  Dim j As Range, r As Range
  Dim lr1 As Long, lr2 As Long
  
  lr2 = 1
  Set r = Range("A:C")      'columns to merge
  For Each j In r.Columns
    lr1 = Cells(Rows.Count, j.Column).End(3).Row
    r.Cells(lr2, r.Columns.Count + 1).Resize(lr1).Value = j.Range(Cells(1, 1), Cells(lr1, 1)).Value
    lr2 = r.Cells(Rows.Count, r.Columns.Count + 1).End(3).Row + 1
  Next
End Sub