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

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

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

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

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

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

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

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

--

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

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

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

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

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

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

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

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

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

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

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