Merging data

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)

Hi Sonic
Try this code, it assumes that you want to merge from row 1 to the last row,
and that the last row is determined by the last row in coumn D. Change
these if it is something else. I also added a comma separator between
column data.

Sub MergeD()
Dim xLr As Long, xr As Long
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
Cells(xr, 4) = Trim(Cells(xr, 5)) & "," _
& Trim(Cells(xr, 6)) & "," _
& Trim(Cells(xr, 7))
Next xr
End Sub

--
Cheers
Nigel



"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 cannot get that code to produce the requested answer (being not to clever with vb code) but the answer for a 2-pair set is

=IF(ISERR(FIND(",",E1)),E1&"("&F1&")",LEFT(E1,(FIND(",",E1)-1))&"("&LEFT(F1,(FIND(",",F1)-1))&"),"&MID(E1,(FIND(",",E1)+1),6)&"("&MID(F1,(FIND(",",F1)+1),6)&")")

which I would then have to copy & paste-special Values over the column and remove the no-longer-required columns (yeah, I know, manual crap) - but the selection is correct.

Originally Posted by Nigel

Hi Sonic
Try this code, it assumes that you want to merge from row 1 to the last row,
and that the last row is determined by the last row in coumn D. Change
these if it is something else. I also added a comma separator between
column data.

Sub MergeD()
Dim xLr As Long, xr As Long
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
Cells(xr, 4) = Trim(Cells(xr, 5)) & "," _
& Trim(Cells(xr, 6)) & "," _
& Trim(Cells(xr, 7))
Next xr
End Sub

--
Cheers
Nigel



"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 Nigel for your help.

However, That merges the data in columns D and E, but in the wrong order. I’m not sure if this is possible, but I would like to merge the first parts before the commas, in both columns D and E together. Then place a comma and then merge the remainder of columns D and E together (after the commas in columns D and E). It’s hard to explain, so I have constructed a rough diagram to try indicate what I would like, if possible. Column F is the result that I’m trying to achieve. Thank you for your help.

Thanks very much Bryan,

your better than me.
I don't even know how to Implement your code, sorry

I'll take a look but you originally asked to concatenate the columns - which
means exactly what I provided!

--
Cheers
Nigel



"sonic" <sonic.1tr12g_1124013929.1134@excelforum-nospam.com> wrote in
message news:sonic.1tr12g_1124013929.1134@excelforum-nospam.com...
>
> Thanks very much Nigel for your help.
>
> However, That merges the data in columns D and E, but in the wrong
> order. I'm not sure if this is possible, but I would like to merge the
> first parts before the commas, in both columns D and E together. Then
> place a comma and then merge the remainder of columns D and E together
> (after the commas in columns D and E). It's hard to explain, so I have
> constructed a rough diagram to try indicate what I would like, if
> possible. Column F is the result that I'm trying to achieve. Thank
> you for your help.
>
>
> --
> sonic
> ------------------------------------------------------------------------
> sonic's Profile:
http://www.excelforum.com/member.php...o&userid=23060
> View this thread: http://www.excelforum.com/showthread...hreadid=395624
>

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.