Concatenate 2 columns to 1 in a loop across excel spreadsheet

[Rinku75]

Hi! How do I concatenate ​2 columns to 1 - recurrently - till the last blank column?


For example, see below the data. How do I combine
Columns A and B to a new column after F (i.e., G),
Columns C and D to a new column after G,
Columns E and F to a new column after H, and so on?


I have a large database, I want to start concatenating from Column H to Column MH (there are 761 rows)


I have spent hours trying to figure this out and have not been able to come up a quick way to do this in excel. Any help will be appreciated.

	A	B	C	D	E	F
1	0	0	0	0	0	1
2	1	2	2	3	3	0
3	1	2	2	3	3	0
4	1	2	2	3	3	0
5	1	2	2	3	3	0
6	1	2	2	3	3	0
7	1	2	2	0	0	0

<tbody>
</tbody>

 

[JLGWhiz]

This seems to work

Sub t()
Dim lr As Long, lc As Long
lc = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByColumns, xlPrevious).Column
lr = ActiveSheet.Cells.Find("*", , xlValues, xlPart, xlByRows, xlPrevious).Row
For c = 1 To lc Step 2
    For i = 1 To lr
        With Sheets(1)
            .Cells(i, .Columns.Count).End(xlToLeft).Offset(, 1) = Cells(i, c).Value & .Cells(i, c + 1).Value
        End With
    Next
Next
End Sub

 

[Leith Ross]

Hello Rnku75,

This concatenate the columns by 2 at a time into single new columns starting with column "MI". Concatenation treats all cell values as text and joins them.

In your example you have numbers. Please remember, once numbers are concatenated they can not be used mathematically in formulas.

Sub ConcatenateColumns()


    Dim c       As Long
    Dim BegCol  As Long
    Dim BegRow  As Long
    Dim EndCol  As Long
    Dim EndRow  As Long
    Dim NewCol  As Long
    Dim r       As Long
    
        BegCol = Columns("H").Column
        EndCol = Columns("MH").Column
        NewCol = EndCol + 1
        
        BegRow = 1
        EndRow = 761
        
        For c = BegCol To EndCol Step 2
            For r = BegRow To EndRow
                NewCol = Cells(r, c) & Cells(r, c + 1)
            Next r
            
            NewCol = NewCol + 1
        Next c
        
End Sub

 

[Rinku75]

Hi Leith: I tried running this macro and got the following error msg:

Run-time error "13"
Type Mismatch

And when I hit debug, the following sentence got highlighted:
NewCol = Cells(r, c) & Cells(r, c + 1)

Can you help fix this?

Thanks!

Hello Rnku75,

This concatenate the columns by 2 at a time into single new columns starting with column "MI". Concatenation treats all cell values as text and joins them.

In your example you have numbers. Please remember, once numbers are concatenated they can not be used mathematically in formulas.

Sub ConcatenateColumns()


    Dim c       As Long
    Dim BegCol  As Long
    Dim BegRow  As Long
    Dim EndCol  As Long
    Dim EndRow  As Long
    Dim NewCol  As Long
    Dim r       As Long
    
        BegCol = Columns("H").Column
        EndCol = Columns("MH").Column
        NewCol = EndCol + 1
        
        BegRow = 1
        EndRow = 761
        
        For c = BegCol To EndCol Step 2
            For r = BegRow To EndRow
                NewCol = Cells(r, c) & Cells(r, c + 1)
            Next r
            
            NewCol = NewCol + 1
        Next c
        
End Sub

 

[Leith Ross]

Hello Rinku75,

This version works.

Sub ConcatenateColumns()


    Dim c       As Long
    Dim BegCol  As Long
    Dim BegRow  As Long
    Dim EndCol  As Long
    Dim EndRow  As Long
    Dim NewCol  As Long
    Dim r       As Long
    
        BegCol = Columns("H").Column
        EndCol = Columns("MH").Column
        NewCol = EndCol + 1
        
        BegRow = 1
        EndRow = 761
        
        Application.ScreenUpdating = False
        
        For c = BegCol To EndCol Step 2
            For r = BegRow To EndRow
                Cells(r, NewCol) = Cells(r, c) & Cells(r, c + 1)
            Next r
            
            NewCol = NewCol + 1
        Next c
        
        Application.ScreenUpdating = True
        
End Sub

 

[Peter_SSs]

I want to start concatenating from Column H to Column MH

Column H to column MH is 339 columns. How would an odd number of columns get concatenated in pairs?

 

[Peter_SSs]

Assuming ..
- there is an even number of columns (I've used H:MG)
- there is data in all the cells, so that the last column can be used to determine the last row of data.
.. then with your reasonably large data set, this should be considerably faster.

Sub ConcatPairs()
  Dim a As Variant
  Dim i As Long, j As Long
  
  a = Range("H1", Range("MG" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2) Step 2
      a(i, (j + 1) / 2) = a(i, j) & a(i, j + 1)
    Next j
  Next i
  Range("MH1").Resize(UBound(a), UBound(a, 2) / 2).Value = a
End Sub

 

[Rick Rothstein]

Assuming ..
- there is an even number of columns (I've used H:MG)
- there is data in all the cells, so that the last column can be used to determine the last row of data.
.. then with your reasonably large data set, this should be considerably faster.

Sub ConcatPairs()
  Dim a As Variant
  Dim i As Long, j As Long
  
  a = Range("H1", Range("MG" & Rows.Count).End(xlUp)).Value
  For i = 1 To UBound(a)
    For j = 1 To UBound(a, 2) Step 2
      a(i, (j + 1) / 2) = a(i, j) & a(i, j + 1)
    Next j
  Next i
  Range("MH1").Resize(UBound(a), UBound(a, 2) / 2).Value = a
End Sub

While your code is faster (0.031 seconds compared to 0.047 seconds on my computer), the difference is not significant in human terms. With that said, here is another way to do it...

[table="width: 500"]
[tr]
	[td]Sub ConcatColumnPairs()
  With Range("H1", Cells(Rows.Count, "MG").End(xlUp))
    .Offset(, .Columns.Count).Resize(, .Columns.Count / 2) = Application.Index(Evaluate("IF(MOD(COLUMN(" & .Address & ")-COLUMN(" & .Cells(1).Address & ")+1,2)," & .Address & "&" & .Offset(, 1).Address & ","""")"), Evaluate("ROW(" & .Address & ")"), Evaluate("2*(COLUMN(" & .Address & ")-COLUMN(" & .Cells(1).Address & "))+1"))
  End With
End Sub[/td]
[/tr]
[/table]

 

[Peter_SSs]

0.031 seconds compared to 0.047 seconds

For me, with 348 columns and 761 rows like the OP, it is 0.328 v 1.109 which is certainly noticeable.

 

[Rick Rothstein]

For me, with 348 columns and 761 rows like the OP, it is 0.328 v 1.109 which is certainly noticeable.

You are right... I forgot to increase the number of rows to 761 when I did my test.