Concatenate 2 columns to 1 in a loop across excel spreadsheet

R

Rinku75

New Member
Joined
Jun 24, 2016
Messages
2
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.

[TABLE="width: 448"]
<tbody>[TR]
[TD]
[/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[TD]E
[/TD]
[TD]F
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
This seems to work
Code: 
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
 
Upvote 0
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.

Code: 
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
 
Last edited:
Upvote 0
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!

Leith Ross said:
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.

Code: 
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
Click to expand...
 
Upvote 0
Hello Rinku75,

This version works.

Code: 
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
 
Upvote 0
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.

Rich (BB code): 
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
 
Upvote 0
Peter_SSs said:
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.

Rich (BB code): 
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
Click to expand...
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...
Code: 
[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]
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
VBA to merge 2 tables across many sheets
Replies
1
Views
140
alansidman
A
S
  • Solved
Concatenate Numerous rows based off cells value
Replies
4
Views
281
hagia_sofia
hagia_sofia
serge
  • Question Question
#N/A cell skip.
Replies
3
Views
247
Peter_SSs
Peter_SSs
S
  • Solved
Help with Excel Chart
Replies
8
Views
143
myall_blues
myall_blues
B
  • Question Question
Can I concatenate multiple results into 1 field?
Replies
5
Views
294
bruty
B

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top