Converting data into multiple rows

[Kathleen_TX]

I have a spreadsheet of raw data with several columns. One of the columns
contains multiple market segments separated by commas (Chemicals, Specialty
Chemicals, Polymers, etc). I want to break out each row into multiple rows
based on however many market segments there are. For example, one row of data
may have 6 market segments. I want to convert that row of data into 6 rows of
data, one per market segment. Is there an easy way to do this? I have 1000+
rows of data, so doing this manually is extremely painful.

 

[Mike H]

Kathleen,

First ensure you have sufficient empty columns to the right of your column
of data to split the data into then:

Data|Text to columns - delimited - next - select comma - finish


Mike

 

[Kathleen_TX]

Thanks Mike. I tried that, but that's not exactly what I'm trying to do. I
want to break out the entire row of data into multiple rows.

 

[Patrick Molloy]

this sub checks the contents of column C and adds new rows for each item if
theres a cell with more than one item comma separated

Option Explicit
Sub breakup()
Dim cell As Range
Dim rownum As Long
Dim data As Variant
Dim index As Long
'insert from bottom to top
'get the last row
rownum = Range("C1").End(xlDown).Row
For rownum = rownum To 2 Step -1
If InStr(Cells(rownum, "C").Value, ",") > 0 Then
data = Split(Cells(rownum, "C"), ",")
For index = 0 To UBound(data, 1)
Rows(rownum + 1).Insert
Cells(rownum + 1, "C") = data(index)
Next
Rows(rownum).Delete
End If
Next
End Sub

 

[Kathleen_TX]

Thanks Patrick. I think we must be close. The sub created new rows with
column C split out, but the rest of the row data wasn't replicated.

In this spreadsheet I have several columns of data - Title, Description,
Click Here, Market Segment, Business Area, Application, Product, etc. The
Market Segment column has multiple entries separated by commas. I need the
entire row replicated x number of times based on how many entries are in the
Market column, with a single market per row.

 

[Patrick Molloy]

ok. when the row is added , replicate the data before updating the value in
C

Option Explicit
Sub breakup()
Dim cell As Range
Dim rownum As Long
Dim data As Variant
Dim index As Long
'insert from bottom to top
'get the last row
rownum = Range("C1").End(xlDown).Row
For rownum = rownum To 2 Step -1
If InStr(Cells(rownum, "C").Value, ",") > 0 Then
data = Split(Cells(rownum, "C"), ",")
For index = 0 To UBound(data, 1)
Rows(rownum + 1).Insert

Rows(rownum).Copy
Rows(rownum + 1).PasteSpecial xlAll

Cells(rownum + 1, "C") = data(index)
Next
Rows(rownum).Delete
End If
Next
End Sub

 

[Kathleen_TX]

Thanks Patrick.

ok. when the row is added , replicate the data before updating the value in
C

Option Explicit
Sub breakup()
Dim cell As Range
Dim rownum As Long
Dim data As Variant
Dim index As Long
'insert from bottom to top
'get the last row
rownum = Range("C1").End(xlDown).Row
For rownum = rownum To 2 Step -1
If InStr(Cells(rownum, "C").Value, ",") > 0 Then
data = Split(Cells(rownum, "C"), ",")
For index = 0 To UBound(data, 1)
Rows(rownum + 1).Insert

Rows(rownum).Copy
Rows(rownum + 1).PasteSpecial xlAll

Cells(rownum + 1, "C") = data(index)
Next
Rows(rownum).Delete
End If
Next
End Sub