Merging data

To expand on Nigel's code and group the components they way your example
shows, use this:

Sub MergeD()
Dim xLr As Long, xr As Long
Dim Dl As String 'left of comma col D
Dim Dr As String 'right of comma col D
Dim El As String 'left of comma col E
Dim Er As String 'right of comma col E
Dim MyStr As String ' the comma
Dim MyPos As Integer ' position of comma
Dim Lngth As Long 'length of string
Dim MyChar1 As String
Dim Mychar2 As String

MyStr = ","
MyChar1 = "("
Mychar2 = ")"
xLr = Cells(Rows.Count, "D").End(xlUp).Row ' column D determines lastrow

Cells(1, 4).EntireColumn.Insert
For xr = 1 To xLr ' range from row 1 to lastrow
Dl = Cells(xr, 5)
Lngth = Len(Dl)
MyPos = InStr(Dl, MyStr)
If MyPos = 0 Then
Dl = Dl
Else
Dr = Right(Dl, Lngth - MyPos)
Dl = Left(Dl, Lngth - MyPos)
End If

El = Cells(xr, 6)
Lngth = Len(El)
MyPos = InStr(El, MyStr)
If MyPos = 0 Then
El = El
Else
Er = Right(El, Lngth - MyPos)
El = Left(El, Lngth - MyPos)
End If

If MyPos = 0 Then
Cells(xr, 4) = Dl & MyChar1 & El & Mychar2
Else
Cells(xr, 4) = Dl & MyChar1 & El & Mychar2 _
& MyStr & " " & Dr & MyChar1 & Er & Mychar2
End If

Next xr
End Sub

This works with the data examples given
Mike F

"sonic" <sonic.1tqn6m_1123995933.7453@excelforum-nospam.com> wrote in
message news:sonic.1tqn6m_1123995933.7453@excelforum-nospam.com...
>
> Is there a script that can concatenate and replace all columns D and E
> to column F and then move this into a new column D?
> Thanks very much,
> Robert.
>
> _EXAMPLE__ (-Data in columns D and E could also be alpha-numeric, but
> will always be matched at the same comma points-)
>
> A..........B........C.............D.............E.................F.........
> text....text....text.........1, 2.........3 , 4.............1(3), 2(4)
> text....text....text.........1.............4..................1(4)
> text....text....text.........1.............6..................1(6)
> text....text....text.........1, 6.........7 , 4.............1(7), 6(4)
> text....text....text.........1, 8.........9 , 4.............1(9), 8(4)
> text....text....text.........1.............10................1(10)
> text....text....text.........1, 10.......11 , 4...........1(11), 10(4)
> text....text....text.........1, 12.......13 , 4...........1(13), 12(4)
> text....text....text.........1.............14................1(14)
> text....text....text.........1.............16................1(16)
>
>
> --
> sonic
> ------------------------------------------------------------------------
> sonic's Profile:
> http://www.excelforum.com/member.php...o&userid=23060
> View this thread: http://www.excelforum.com/showthread...hreadid=395624
>

I forgot you wanted to delete the original data, so put this line at the end
before End Sub like so:

Next xr 'existing line
Range("E1:F" & xLr).Delete 'new line
End Sub

Mike F
"Mike Fogleman" <mikefogleman@insightbb.com> wrote in message
news:%23kG6zOPoFHA.2904@tk2msftngp13.phx.gbl...
> To expand on Nigel's code and group the components they way your example
> shows, use this:
>
> Sub MergeD()
> Dim xLr As Long, xr As Long
> Dim Dl As String 'left of comma col D
> Dim Dr As String 'right of comma col D
> Dim El As String 'left of comma col E
> Dim Er As String 'right of comma col E
> Dim MyStr As String ' the comma
> Dim MyPos As Integer ' position of comma
> Dim Lngth As Long 'length of string
> Dim MyChar1 As String
> Dim Mychar2 As String
>
> MyStr = ","
> MyChar1 = "("
> Mychar2 = ")"
> xLr = Cells(Rows.Count, "D").End(xlUp).Row ' column D determines lastrow
>
> Cells(1, 4).EntireColumn.Insert
> For xr = 1 To xLr ' range from row 1 to lastrow
> Dl = Cells(xr, 5)
> Lngth = Len(Dl)
> MyPos = InStr(Dl, MyStr)
> If MyPos = 0 Then
> Dl = Dl
> Else
> Dr = Right(Dl, Lngth - MyPos)
> Dl = Left(Dl, Lngth - MyPos)
> End If
>
> El = Cells(xr, 6)
> Lngth = Len(El)
> MyPos = InStr(El, MyStr)
> If MyPos = 0 Then
> El = El
> Else
> Er = Right(El, Lngth - MyPos)
> El = Left(El, Lngth - MyPos)
> End If
>
> If MyPos = 0 Then
> Cells(xr, 4) = Dl & MyChar1 & El & Mychar2
> Else
> Cells(xr, 4) = Dl & MyChar1 & El & Mychar2 _
> & MyStr & " " & Dr & MyChar1 & Er & Mychar2
> End If
>
> Next xr
> End Sub
>
> This works with the data examples given
> Mike F
>
> "sonic" <sonic.1tqn6m_1123995933.7453@excelforum-nospam.com> wrote in
> message news:sonic.1tqn6m_1123995933.7453@excelforum-nospam.com...
>>
>> Is there a script that can concatenate and replace all columns D and E
>> to column F and then move this into a new column D?
>> Thanks very much,
>> Robert.
>>
>> _EXAMPLE__ (-Data in columns D and E could also be alpha-numeric, but
>> will always be matched at the same comma points-)
>>
>> A..........B........C.............D.............E.................F.........
>> text....text....text.........1, 2.........3 , 4.............1(3), 2(4)
>> text....text....text.........1.............4..................1(4)
>> text....text....text.........1.............6..................1(6)
>> text....text....text.........1, 6.........7 , 4.............1(7), 6(4)
>> text....text....text.........1, 8.........9 , 4.............1(9), 8(4)
>> text....text....text.........1.............10................1(10)
>> text....text....text.........1, 10.......11 , 4...........1(11), 10(4)
>> text....text....text.........1, 12.......13 , 4...........1(13), 12(4)
>> text....text....text.........1.............14................1(14)
>> text....text....text.........1.............16................1(16)
>>
>>
>> --
>> sonic
>> ------------------------------------------------------------------------
>> sonic's Profile:
>> http://www.excelforum.com/member.php...o&userid=23060
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=395624
>>
>
>

Thanks very much Mike Fogleman for your help. Perfect!!...Exactly what I needed!!

Also to Nigel (who has helped me before!) and Bryan Hessey also.

Robert.