Add new row after each comma and copy data from the row

[kreepa]

I have a spreadsheet:

It has columns A - Z

In Column Z, I have a list separated by commas.
For example: bird, dog, cat, goat, etc

Each list in column Z may contain a different number of items in the list (some hold 2 some may hold 5 or more)

Desired Output:
In Column Z - At each comma, add it to column z, as a value on the row below and copy all of the data from the related column.
For Example:
bird
dog
cat
goat

and then copy the related data down.

Thanks for your help.

 

[Peter_SSs]

Re: Add new row after each coma and copy data from the row

Not certain this is what you want, but give it a try ona copy of your workbook. Post back if you need help with how to implement the code. I have assumed that the items in the lists are separated by a comma followed by a space.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Lists()<br>    <SPAN style="color:#00007F">Dim</SPAN> aList<br>    <SPAN style="color:#00007F">Dim</SPAN> c <SPAN style="color:#00007F">As</SPAN> Range<br>    <SPAN style="color:#00007F">Dim</SPAN> nr <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, nRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    Columns("AA").Insert<br>    nr = 1<br>    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> c <SPAN style="color:#00007F">In</SPAN> Range("Z1", Range("Z" & Rows.Count).End(xlUp))<br>        aList = Split(c.Value, ", ")<br>        nRows = 1 + <SPAN style="color:#00007F">UBound</SPAN>(aList)<br>        Range("AA" & nr).Resize(nRows).Value = Application.Transpose(aList)<br>        nr = nr + nRows<br>    <SPAN style="color:#00007F">Next</SPAN> c<br>    Columns("Z").Delete<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

 

[stanleydgromjr]

Re: Add new row after each coma and copy data from the row

Peter,

Your code has taught me so much.

I hope you don't mind.

I am assuming that kreepa needs cells in column Z copied down (like you have done), and then the data in column A thru Y copied down also.


Sample data before the updated macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	3	dog, cat, bird
2	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	1	4	dog, cat, bird, tree
3	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	1	3	dog, cat, bird
4	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1	5	dog, cat, bird, tree, house
5	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	1	3	dog, cat, bird
Sheet1

After the updated macro:

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z
1	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	3	dog
2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	3	cat
3	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	2	1	3	bird
4	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	1	4	dog
5	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	1	4	cat
6	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	1	4	bird
7	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	3	1	4	tree
8	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	1	3	dog
9	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	1	3	cat
10	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	4	1	3	bird
11	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1	5	dog
12	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1	5	cat
13	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1	5	bird
14	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1	5	tree
15	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	5	1	5	house
16	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	1	3	dog
17	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	1	3	cat
18	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	6	1	3	bird
Sheet1

Please TEST this FIRST in a COPY of your workbook (always make a backup copy before trying new code, you never know what you might lose).

Press and hold down the 'ALT' key, and press the 'F11' key.

On the 'Insert' menu, click 'Module'.

Copy the below updated code, and paste it into the Module (on the right pane).

Option Explicit
Sub Lists()
Dim aList
Dim c As Range
Dim nr As Long, nRows As Long, LR As Long, i As Long
Application.ScreenUpdating = False
Columns("AA").Insert
nr = 1
LR = Cells(Rows.Count, "Z").End(xlUp).Row
For i = LR To 1 Step -1
  aList = Split(Cells(i, "Z").Value, ", ")
  nRows = 1 + UBound(aList)
  Range("AA" & i + 1 & ":AA" & i + 1 + nRows - 2).EntireRow.Insert
  Range("AA" & i).Resize(nRows).Value = Application.Transpose(aList)
  Range("A" & i & ":Y" & i).Copy Range("A" & i + 1 & ":A" & i + nRows - 1)
Next i
Columns("Z").Delete
Application.ScreenUpdating = True
End Sub

Then run macro "Lists".


Have a great day,
Stan

 

[Peter_SSs]

Re: Add new row after each coma and copy data from the row

Stan

Yes, you're probably right about what the OP wanted. My suggested code would be as below. I have pretty much followed your modifications but ...
a) eliminated the variables no longer used.
b) (even though it sounded like this was not the case) allowed for the case where data is beyond column Z as well.
c) allowed for the case where there was only 1 item in column Z.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> Lists()<br>    <SPAN style="color:#00007F">Dim</SPAN> aList<br>    <SPAN style="color:#00007F">Dim</SPAN> nRows <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, LR <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    LR = Cells(Rows.Count, "Z").End(xlUp).Row<br>    <SPAN style="color:#00007F">For</SPAN> i = LR <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1<br>        aList = Split(Cells(i, "Z").Value, ", ")<br>        nRows = 1 + <SPAN style="color:#00007F">UBound</SPAN>(aList)<br>        <SPAN style="color:#00007F">If</SPAN> nRows > 1 <SPAN style="color:#00007F">Then</SPAN><br>            Rows(i).Copy<br>            Rows(i + 1).Resize(nRows - 1).Insert Shift:=xlDown<br>            Cells(i, "Z").Resize(nRows).Value = Application.Transpose(aList)<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#00007F">Next</SPAN> i<br>    Application.CutCopyMode = <SPAN style="color:#00007F">False</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>