Merging data
 

Is there a script that can concatenate and replace all columns D and
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, bu
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

--
soni
-----------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...fo&userid=2306
View this thread: http://www.excelforum.com/showthread.php?threadid=39562

Merging data
 

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" wrote in
message ...

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

Merging data
 

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,(FIN D(",",E1)-1))&"("&LEFT(F1,(FIND(",",F1)-1))&"),"&MID(E1,(FIND(",",E1)+1),6)&"("&MID(F1,(FI ND(",",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.



Nigel Wrote:
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" wrote in
message ...

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



--
Bryan Hessey
------------------------------------------------------------------------
Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059
View this thread: http://www.excelforum.com/showthread...hreadid=395624

Merging data
 

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

Merging data
 

Thanks very much Bryan,

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


--
sonic
------------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=395624

Merging data
 

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

--
Cheers
Nigel



"sonic" wrote in
message ...

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

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" wrote in
message ...

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

Merging data
 

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" wrote in message
...
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" wrote in
message ...

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

Merging data
 

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.


--
sonic
------------------------------------------------------------------------
sonic's Profile: http://www.excelforum.com/member.php...o&userid=23060
View this thread: http://www.excelforum.com/showthread...hreadid=395624