| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to concat row
Hi,
A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
could you provide an example?
|
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
Public Sub ProcessData()
Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
Thank you both for your replies. What I'm trying to do is make this:
52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
Also after this I'd like to separate them into columns.
-- By persisting in your path, though you forfeit the little, you gain the great. "DavidH56" wrote: Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
What is the logic rule?
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
Two new columns are to be inserted for A items (Actual flag) dates (one for
actual start and one for actual finish). Therefore rows without A's will be be blank in these columns. The Actual flag columns should be inserted after its date respectively. Thanks for your help. -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: What is the logic rule? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
Sorry I forgot,
Ther will be 7 columns A-ID, B-%, C-Start Date, D-Actual start flag, D-Finish Date, E-Actual Finish Flag, F-Remaining Days. Thanks again. -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: What is the logic rule? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
I made a mistake,
The rule should be if for cells in column A<14 characters, then append it to the text string on the row above it. Sorry and thank you. -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: What is the logic rule? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
Sub Reorganise()
Dim i As Long With ActiveSheet i = 1 Do If Len(.Cells(i, "A").Value) < 13 Then .Cells(i - 1, "D").Value = .Cells(i, "A").Value .Cells(i + 1, "A").Resize(, 3).Copy .Cells(i - 1, "E") .Rows(i).Resize(2).Delete End If i = i + 1 Loop Until .Cells(i, "A").Value = "" End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... I made a mistake, The rule should be if for cells in column A<14 characters, then append it to the text string on the row above it. Sorry and thank you. -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: What is the logic rule? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Macro to concat row
This code works to perfection. Thank you so very much Bob for your expertise.
-- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Sub Reorganise() Dim i As Long With ActiveSheet i = 1 Do If Len(.Cells(i, "A").Value) < 13 Then .Cells(i - 1, "D").Value = .Cells(i, "A").Value .Cells(i + 1, "A").Resize(, 3).Copy .Cells(i - 1, "E") .Rows(i).Resize(2).Delete End If i = i + 1 Loop Until .Cells(i, "A").Value = "" End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... I made a mistake, The rule should be if for cells in column A<14 characters, then append it to the text string on the row above it. Sorry and thank you. -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: What is the logic rule? -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Thank you both for your replies. What I'm trying to do is make this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 Look like this: 52TIN54104R02 0% 29-Jun-08 06-Jul-08 5 52TIN54104R03 0% 29-Jun-08 06-Jul-08 5 52TIN54104P07 0% 12-Jun-08 07-Jul-08 20 52TIN54104R40 0% 05-Jul-08 11-Jul-08 5 52TIN54104R41 0% 07-Jul-08 14-Jul-08 5 52TIN54104T11 0% 06-Jul-08 12-Jul-08 5 52TIN54104T12 0% 08-Jul-08 14-Jul-08 5 52TIN54104P08 0% 20-Jun-08 15-Jul-08 19 52TIN54104R45 0% 15-Jul-08 21-Jul-08 5 52TIN54104R46 0% 15-Jul-08 21-Jul-08 5 52TIN54104T13 0% 16-Jul-08 22-Jul-08 5 52TIN54104T14 0% 16-Jul-08 22-Jul-08 5 52TIN54104T02 0% 30-Jun-08 07-Jul-08 5 52TIN54104T03 0% 30-Jun-08 07-Jul-08 5 52TIN54104R04 0% 02-Jul-08 09-Jul-08 5 52TIN54104R05 0% 06-Jul-08 13-Jul-08 5 52TIN54104T05 0% 02-Jul-08 09-Jul-08 5 52TIN54104T06 0% 07-Jul-08 13-Jul-08 5 52TIN54104R06 0% 15-Jul-08 21-Jul-08 5 52TIN54104T08 0% 16-Jul-08 22-Jul-08 5 52TIN12312P02 100% 02-Apr-08 A 29-May-08 A 0 52TIN12312P01 100% 02-Apr-08 A 29-May-08 A 0 52TIN12316P01 71.28% 28-Apr-08 A 07-Aug-08 23 52TIN63101R01 0% 02-Jun-08 11-Jun-08 7 52TIN63406A02 100% 18-May-08 A 25-May-08 A 0 -- By persisting in your path, though you forfeit the little, you gain the great. "Bob Phillips" wrote: Public Sub ProcessData() Dim i As Long Dim LastRow As Long With ActiveSheet LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row For i = LastRow To 2 Step -1 If InStrRev(" " & .Cells(i - 1, "A").Value, " ") < _ Len(.Cells(i - 1, "A").Value & .Cells(i, "A").Value) - 8 Then .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & " " End If .Cells(i - 1, "A").Value = .Cells(i - 1, "A").Value & .Cells(i, "A").Value .Cells(i, "A").Delete Next i End With End Sub -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "DavidH56" wrote in message ... Hi, A have raw data in column A that I would like to clean up. I would like a macro to concatenate each row's data (add it to the end of the previous row) with a space to precede it if the number of characters to the last space is less than seven(7). I would then like to delete that row. I would like to have this loop to the last row for column A. Any help would be greatly appreciated. Thank you. -- By persisting in your path, though you forfeit the little, you gain the great. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| concat rows | Excel Worksheet Functions | |||
| howto: concat (x1:x3) | New Users to Excel | |||
| Concat Macro help... | Excel Discussion (Misc queries) | |||
| Concat Variant Values | Excel Programming | |||
| Concat + dates | Excel Programming | |||
Linear Mode
