Moving text from a line to a new line based on a condition

[Melanie]

I've been trying to do this with a macro but I think VB is needed and I'm
"not" a VB developer so I really hope one of you can help me out. This is the
last major thing I need to make this thing work.

I have records with columns Group, FName, LName, and Combined.
If there are more than 30 characters in the Combined field, I need to move
the full names to the next line, remove the "^" at the end or beginning of
the line (depending on what was moved) and copy the other fields in the
original line. Uploading the list otherwise makes the program bomb for some
reason. I hope someone out there can help me.

group name combined
marketing mjagger mjagger^rthomas^xbono
marketing rthomas mjagger^rthomas^xbono
marketing xbono mjagger^rthomas^xbono
accounting rcharles rcharles^jbrown
accounting jbrown rcharles^jbrown
hr jmayer jmayer
it jjohnson
jjohnson^bdylan^joplin^jfoggerty^braitt
it bdylan
jjohnson^bdylan^joplin^jfoggerty^braitt
it jjoplin
jjohnson^bdylan^joplin^jfoggerty^braitt
it jfoggerty
jjohnson^bdylan^joplin^jfoggerty^braitt
it braitt
jjohnson^bdylan^joplin^jfoggerty^braitt

The example above would become:

group name combined
marketing mjagger mjagger^rthomas^xbono
marketing rthomas mjagger^rthomas^xbono
marketing xbono mjagger^rthomas^xbono
accounting rcharles rcharles^jbrown
accounting jbrown rcharles^jbrown
hr jmayer jmayer
it jjohnson jjohnson^bdylan^joplin
it jjohnson jfoggerty^braitt
it bdylan jjohnson^bdylan^joplin
it bdylan jfoggerty^braitt
it jjoplin jjohnson^bdylan^joplin
it jjoplin jfoggerty^braitt
it jfoggerty jjohnson^bdylan^joplin
it jfoggerty jfoggerty^braitt
it braitt jjohnson^bdylan^joplin
it braitt jfoggerty^braitt

 

[Joel]

This is pretty simple. I just check in Column A for a ^ on the next row and
if it is found move the data and delete next row.


Sub CombineRows()

RowCount = 2
Do While Range("A" & RowCount) <> ""
If InStr(Range("A" & (RowCount + 1)), "^") > 0 Then
Range("C" & RowCount) = Range("A" & (RowCount + 1))
Rows(RowCount + 1).Delete
End If
RowCount = RowCount + 1
Loop
End Sub

 

[Melanie]

I'm not sure about what you have. I'm not trying to combine rows.

I can only have 30 characters in column C(Combined). If I have more than 30
characters in column C for someone then another line should be created and
any names that don't fit within the 30 characters would be move to a new line
that would have to be created. So, for
jjohnson^bdylan^joplin^jfoggerty^braitt, the character 30 is the "t" in
jfoggerty. The combined names "jfoggerty^braitt" would be move to a new line
just below it and the other columns copied from line above.

 

[Joel]

The way the data was posted I thought it was two lines instead of one

The code is much simpler if you put each name on its own line rather than
split after 30 characters. Try this.

Sub SplitRows()

RowCount = 2
Do While Range("A" & RowCount) <> ""
Data = Range("C" & RowCount)
If InStr(Data, "^") > 0 Then
FirstName = Left(Data, InStr(Data, "^") - 1)
Data = Mid(Data, InStr(Data, "^") + 1)
Rows(RowCount + 1).Insert
Range("C" & RowCount) = FirstName
Range("A" & (RowCount + 1)) = Range("A" & RowCount)
Range("B" & (RowCount + 1)) = Range("B" & RowCount)
Range("C" & (RowCount + 1)) = Data
End If
RowCount = RowCount + 1
Loop
End Sub

 

[Melanie]

I'm not used to reading VB code. Does the code copy the other columns (i.e.
group and name) to the new lines? I'll need the data from these fields for
this person to complete the record.

Also, depending on how the data gets loaded it may create multiple records
for this person with the only difference being the value of the combined
field, which may or may not be a problem. If not, great. I'll need to check
to see how this would load into the database and get back to you.

Thx! Melanie

 

[Joel]

The code simply check for a ^ in column C. If the is a ^ it creates a new
row and copies Columns A & B. Then takes First string before ^ and puts in
Column C. the remaining part of the string goes in the next row. Then moves
down one row and repeats.

 

[Melanie]

Thanks for the explanation. I'm trying to find out if it will work to load
the combined field in separate records. I'll have to let you know if we can
do it this way, but thanks for thinking outside the box. Your code is much
simpler that how I envisioned it working