Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Strip carriage return, add row
I've got a sheet with 2 columns - column A has multiple values in one cell
that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#2
|
|||
|
|||
I don't think you can do this with a formula. You would probably need a
fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#3
|
|||
|
|||
Here's an example of such code
Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#4
|
|||
|
|||
Nice, Bob! You're a braver man than I am <g.
Regards, Vasant "Bob Phillips" wrote in message ... Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#5
|
|||
|
|||
Bob,
Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#6
|
|||
|
|||
Do you mean that you want to insert some new blank columns? If so, how many,
and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#7
|
|||
|
|||
Hi Vasant,
Split did the hard work, breaking the line up and providing the count :-) Regards Bob "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... Nice, Bob! You're a braver man than I am <g. Regards, Vasant "Bob Phillips" wrote in message ... Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#8
|
|||
|
|||
Bob,
No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#9
|
|||
|
|||
-- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#10
|
|||
|
|||
Sorry, bear with me, I am still not getting this precisely.
Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#11
|
|||
|
|||
I'll certainly try:
Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#12
|
|||
|
|||
As the before sheet only has one row, I assume that the multiple items in a
cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#13
|
|||
|
|||
Hrmph . . didn't work. Nothing happened.
Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#14
|
|||
|
|||
Is the data in A1:D1?
Bob "adin" wrote in message ... Hrmph . . didn't work. Nothing happened. Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#15
|
|||
|
|||
Columns A - D are populated, with about 400 or so rows.
Column D has the values with carriage returns. Adin "Bob Phillips" wrote: Is the data in A1:D1? Bob "adin" wrote in message ... Hrmph . . didn't work. Nothing happened. Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#16
|
|||
|
|||
I thought you said the Before sheet onluy had 1 row?
Bob Sub FormatData() Dim iLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert Cells(i, "A").Resize(, 4).Copy Destination:=Cells(i + 1, "A").Resize(cLines) For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "D").Value = aryItems(j) Next j End If Next i End Sub "adin" wrote in message ... Columns A - D are populated, with about 400 or so rows. Column D has the values with carriage returns. Adin "Bob Phillips" wrote: Is the data in A1:D1? Bob "adin" wrote in message ... Hrmph . . didn't work. Nothing happened. Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#17
|
|||
|
|||
I get a syntax error - compile error on this line:
Cells(i, "A").Resize(, 4).Copy Destination:=Cells(i + 1, "A").Resize(cLines) My example only had one row - I guess I assumed that a typical spreadsheet would have more than one row. My mistake. Adin "Bob Phillips" wrote: I thought you said the Before sheet onluy had 1 row? Bob Sub FormatData() Dim iLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert Cells(i, "A").Resize(, 4).Copy Destination:=Cells(i + 1, "A").Resize(cLines) For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "D").Value = aryItems(j) Next j End If Next i End Sub "adin" wrote in message ... Columns A - D are populated, with about 400 or so rows. Column D has the values with carriage returns. Adin "Bob Phillips" wrote: Is the data in A1:D1? Bob "adin" wrote in message ... Hrmph . . didn't work. Nothing happened. Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#18
|
|||
|
|||
Bob,
Scratch that syntax error - just had to move the line over. However, we're still not making any changes to the sheet. Adin "Bob Phillips" wrote: I thought you said the Before sheet onluy had 1 row? Bob Sub FormatData() Dim iLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert Cells(i, "A").Resize(, 4).Copy Destination:=Cells(i + 1, "A").Resize(cLines) For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "D").Value = aryItems(j) Next j End If Next i End Sub "adin" wrote in message ... Columns A - D are populated, with about 400 or so rows. Column D has the values with carriage returns. Adin "Bob Phillips" wrote: Is the data in A1:D1? Bob "adin" wrote in message ... Hrmph . . didn't work. Nothing happened. Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
#19
|
|||
|
|||
Send me your workbook, let's look at close range :-)
-- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Scratch that syntax error - just had to move the line over. However, we're still not making any changes to the sheet. Adin "Bob Phillips" wrote: I thought you said the Before sheet onluy had 1 row? Bob Sub FormatData() Dim iLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems iLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = iLastRow To 1 Step -1 aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert Cells(i, "A").Resize(, 4).Copy Destination:=Cells(i + 1, "A").Resize(cLines) For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "D").Value = aryItems(j) Next j End If Next i End Sub "adin" wrote in message ... Columns A - D are populated, with about 400 or so rows. Column D has the values with carriage returns. Adin "Bob Phillips" wrote: Is the data in A1:D1? Bob "adin" wrote in message ... Hrmph . . didn't work. Nothing happened. Adin "Bob Phillips" wrote: As the before sheet only has one row, I assume that the multiple items in a cell are unquantified :-). Try this Sub BreakOut() Dim i As Long Dim cLines As Long Dim aryItems aryItems = Split(Cells(1, "D").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Range("A1:C1").Copy Destination:=Range("A2:A" & cLines + 1) For i = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + 1, "D").Value = aryItems(i) Next i End If End Sub -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... I'll certainly try: Befo A B C D abc def 1 xyz 123 456 ghi After: A B C D 1 xyz 123 456 abc 2 xyz 123 456 def 3 xyz 123 456 ghi Note that the "before" sheet has only one row. Thanks, Adin "Bob Phillips" wrote: Sorry, bear with me, I am still not getting this precisely. Can you post example data and required results? Thanks Bob "adin" wrote in message ... Bob, No blank columns - just more. In this case, you've got 4 or 5 total columns, and only one of them has the carriage returns - the rest are fine, but you want them all lined up together with the carriage return breaks that your macro creates. Adin "Bob Phillips" wrote: Do you mean that you want to insert some new blank columns? If so, how many, and in front of which column? Also, is this related to the previous question, in that it needs to tie in with that code, or a separate question? -- HTH RP (remove nothere from the email address if mailing direct) "adin" wrote in message ... Bob, Once again - you are the macro maestro! If I need to add columns to the sheet, how do I add them in the macro? Thanks again! "Bob Phillips" wrote: Here's an example of such code Sub FormatData() Dim cLastRow As Long Dim i As Long, j As Long Dim cLines As Long Dim aryItems cLastRow = Cells(Rows.Count, "A").End(xlUp).Row For i = cLastRow To 1 Step -1 aryItems = Split(Cells(i, "A").Value, Chr(10)) cLines = LBound(aryItems) + UBound(aryItems) If cLines 1 Then Cells(i + 1, "A").Resize(cLines).EntireRow.Insert For j = UBound(aryItems) To LBound(aryItems) Step -1 Cells(i + j, "A").Value = aryItems(j) Cells(i + j, "B").Value = Cells(i, "B").Value Next j End If Next i End Sub -- HTH RP (remove nothere from the email address if mailing direct) "Vasant Nanavati" <vasantn *AT* aol *DOT* com wrote in message ... I don't think you can do this with a formula. You would probably need a fairly involved macro. You would need to count the number of line breaks in each cell in column A, add that many rows below each cell, split the string, remove the line breaks, and copy the column B information into the new rows. Not difficult, but quite tedious. -- Vasant "adin" wrote in message ... I've got a sheet with 2 columns - column A has multiple values in one cell that are separated by carriage returns, and column B next to it has only one value per cell. I'd like to not only strip out these carriage returns, but also (the fun part) add rows for each value and maintain the reference to the column next to it. Example: A1 looks like this: Milk Eggs Juice B1 looks like this: Food I'd like A1 to contain the value "Milk" and B1 to contain "Food". A2 should contain "Eggs" and B2 should contain "Food". C1 should say "Juice", and C2 should say "Food". You get the idea. Thanks, Adin |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to find replace text or symbol with carriage return | New Users to Excel | |||
How to insert carriage return in the middle of a text formula to . | Excel Discussion (Misc queries) | |||
Carriage Return in Excel | Excel Discussion (Misc queries) | |||
automatic carriage return | Excel Worksheet Functions | |||
Carriage return in macros | Excel Worksheet Functions |