Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging data from 2 data excel sheets | Excel Discussion (Misc queries) | |||
Merging Data | Excel Discussion (Misc queries) | |||
Merging multiple columns of data into one column of data | Excel Discussion (Misc queries) | |||
Should Merging workbooks pick up new data or only edited data? | Excel Worksheet Functions | |||
Scanning for Duplicate Data in a Column, Merging Data and Finally Removing All Duplicates...? | Excel Programming |