Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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" wrote: 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" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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" wrote: 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" wrote: 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" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving text from a line to a new line based on a condition
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 :-) "Joel" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Challenge - Excel Line Feed Character CHR(10) - How to Delete and keep the text formatting without going ro single line in a cell ? | Excel Worksheet Functions | |||
How to change line chart color based on condition like + and -. | Charts and Charting in Excel | |||
how to delete an excel line based on a condition | Excel Worksheet Functions | |||
average Line created in an existing line graph- based on one cell | Charts and Charting in Excel | |||
A 2 line text showing up in the Cell in Excel prints in 1 line | Excel Discussion (Misc queries) |