Macro to concat row

D

DavidH56

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.
 
B

Bob Phillips

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)
 
D

DavidH56

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 said:
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)
Click to expand...
 
D

DavidH56

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 said:
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
Click to expand...
 
B

Bob Phillips

What is the logic rule?

--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)



DavidH56 said:
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
Click to expand...
 
D

DavidH56

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.
 
D

DavidH56

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.
 
D

DavidH56

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.
 
B

Bob Phillips

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Code to Concat a row in loop 2
Concat a three column range into one cell Then Next... 2
DREAM FEATURE: A macro that moves a row but asks me where to move it 18
Breaking One Row into Multiple Rows using VBA Macro? 2
Add a new row in Named Table (Table1) and add data from txtboxes into last empty cells based on header named of the Table1 0
Macro delete columns satring row 23 3
macro to insert row and type data 2
Concat rows - 13

Top