PDA

View Full Version : Concatenate two columns in a sheet



sindhuja
03-26-2012, 09:29 PM
Hi,

I need to concatenate the two columns (R and S) in sheet 1 and results to be displayed in column T.

i want a new column to be inserted using coding and display the concatenated results in that column. Once this is done the format to be changed as "dd/mm/yyyy" since the column R and S are date values.

Sub ConcatenateRange()
Dim val As String
Dim iLastRow As Long
Dim iLastCol As Long
Dim i As Long
Dim j As Long
iLastRow = Cells.Find(What:="*", _
After:=Range("P1"), _
SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For i = 1 To iLastRow
iLastCol = Cells(i, Columns.Count).End(xlToLeft).Column
val = ""
For j = 2 To iLastCol
val = val & Cells(i, j)
Next j
Cells(i, "R").Value = val
Next i
End Sub


used the coding above, as i need to concatenate only the values in column Q and R i tried editing the coding.. but am not sucessful..

pls help me on this...

-Sindhuja

mancubus
03-26-2012, 11:39 PM
Sub ConcatCols()
'concatenate columns R & S in column T

Dim LastRow As Long

With Worksheets("Sheet1")
LastRow = .Cells(.Rows.Count, "R").End(xlUp).Row
With .Range("T2:T" & LastRow)
.Formula = "=R2&S2"
.Value = .Value
End With
End With

End Sub

sindhuja
03-28-2012, 02:32 AM
I need to format the values in column T as "DD/MM/YYYY"

Help me with this also..

Bob Phillips
03-28-2012, 02:41 AM
.Range("D2").Resize(LastRow - 1) .Numberformat = "dd/mm/yyyy"

Beatrix
07-24-2012, 09:05 AM
Hi Mancubus ,

I was going to use the code you provided in your previous reply below; but it didn't exactly work in my case so I edited it as below by guessing how it would work but failed :doh:Any chance you could help me:help??

I need to Concatenate F&G in column F if G is not blank
cHEERSSS



Sub ConcatCols()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
With .Range("F2:F" & LastRow)
If G <> "" Then
.Formula = "=F2&G2"
.Value = .Value

End With

End With

End If

End Sub

Bob Phillips
07-24-2012, 11:05 AM
Sub ConcatCols()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
End With
End Sub

Beatrix
07-25-2012, 11:26 AM
Hi xld ,

Thanks for your response. Much appreciated.

I tried to put space between 2 cell values in formula below but it wasn't a good try I guess as it didn't work. Any suggestions? Also if 2 cells are blank then cells are filled with 0. If 2 cells are empty then it should remain blank..

Last bit, I need to clear cell content in column G after merge 2 columns..Could you help me on this? Sorry for multiple requirements..

Cheers xld
Yeliz




.Formula = "=IF(H2<>"""",F2&""""&H2,F2)"



Sub ConcatCols()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
End With
End Sub

Bob Phillips
07-25-2012, 02:52 PM
What is the difference between blank and empty?


Sub ConcatCols()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&"" ""&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
.Columns("G").ClearContents
End With
End Sub

Beatrix
07-26-2012, 03:28 AM
Thanksss xld :friends:

no difference between blank and empty, just a repeat :yes My English :devil2:Blank cells were filled with 0 after applying to formula so wanted to remove them and did by editing the formula below


.Formula = "=IF(G2&H2<>"""",F2&"" ""&H2,F2)"


Cheersss xld :thumb

sindhuja
07-26-2012, 05:18 AM
Thank you so much . . . really helpful

Beatrix
08-02-2012, 05:15 AM
Hi xld ,

I need to use this script for another data scenario to concatenate 2 columns again. This time column D has some texts like "Marden House" etc and Column E has some numbers say "36" I need to merge them as "36 Marden House"

Before: D2= Marden House E2= 36

After: D2= 36 Marden House E2 should be blank.

If D2 is blank and E2=36 then nothing to merge.

I've changed cell references in below script it worked but It merged each cell value.:doh: I want to do it for nonblank cells only in column D.

Could you tell me which part I need to change exactly to make it work:help??



Sub ConcatCols2()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)
.Formula = "=IF(D2&F2<>"""",F2&"" ""&D2,D2)"
.Value = .Value
End With
.Columns("F").Delete

End With
End Sub
Cheers
Yeliz







What is the difference between blank and empty?


Sub ConcatCols()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "F").End(xlUp).Row
.Columns("G").Insert
With .Range("G2:G" & LastRow)
.Formula = "=IF(H2<>"""",F2&"" ""&H2,F2)"
.Value = .Value
End With
.Columns("F").Delete
.Columns("G").ClearContents
End With
End Sub

Bob Phillips
08-02-2012, 05:35 AM
Is this what you need Yeliz?

Sub ConcatCols2()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)

.Formula = "=IF(D2<>"""",E2&"" ""&D2,E2)"
.Value = .Value
End With

.Columns("E").Delete
End With
End Sub


Are you working/living in England then?

Beatrix
08-02-2012, 07:06 AM
:( it didn't work. I attached sample file to be clear on the scenario.




Is this what you need Yeliz?

Sub ConcatCols2()
'concatenate columns F & G in column F

Dim LastRow As Long

With Worksheets("split")

LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)

.Formula = "=IF(D2<>"""",E2&"" ""&D2,E2)"
.Value = .Value
End With

.Columns("E").Delete
End With
End Sub

Are you working/living in England then?

Beatrix
08-02-2012, 07:09 AM
sorry xld , just saw your question. Yep I'm in London. You're in the UK too??

Bob Phillips
08-02-2012, 07:21 AM
I'm afraid that doesn't make anything clear to me. Your split worksheet doesn't have columns D & E, and your parseadd macro is failing when I open the workbook.

Beatrix
08-02-2012, 07:53 AM
Oops! I've run other macros on my machine and just saved the last script you sent. Please see new attachment. If column D is non-blank I need to concatenate columns E&D in columnD



I'm afraid that doesn't make anything clear to me. Your split worksheet doesn't have columns D & E, and your parseadd macro is failing when I open the workbook.

Beatrix
08-03-2012, 09:05 AM
this one is working but I couldn't manage to clear the content in column F. not entire column though. F2&"" ""&D2 after merge this two I need to clear the numbers in column F which are merged with cell values in column D.. rest of the cells in F shouldn't be cleared.

Could anyone help me on this ??

Cheers
Yeliz


Sub ConcatCols2()
'concatenate columns E & D in column D

Dim LastRow As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
With .Range("E2:E" & LastRow)
.Formula = "=IF(D2<>"""",F2&"" ""&D2,"""")"
.Value = .Value
End With
.Columns("D").Delete

End With
End Sub

mancubus
08-03-2012, 02:59 PM
hi Yeliz,

if this what you want?


Sub ConcatCols()
'concatenate columns E & D in column D

Dim LastRow As Long, i As Long

With Worksheets("split")
LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
.Columns("E").Insert
For i = 2 To LastRow
If .Cells(i, "D") <> "" Then
.Cells(i, "E") = .Cells(i, "F") & " " & .Cells(i, "D")
.Cells(i, "E").Font.Color = vbRed 'added for testing; may be deleted.
.Cells(i, "F").Clear
End If
Next
.Columns("D").Delete
End With

End Sub

Beatrix
08-06-2012, 03:35 AM
Exactly:cloud9:

Thanksss very much mancubus :bow:

Cheers:thumb

mancubus
08-06-2012, 04:33 AM
you're welcome.

glad it helped.