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.

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" wrote:

> 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.

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.

"Mike H" wrote:

> 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" wrote:
>
> > 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.

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" <(E-Mail Removed)> wrote in message
news:EF03A1BB-93ED-42A4-831D-(E-Mail Removed)...
>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.

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" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:EF03A1BB-93ED-42A4-831D-(E-Mail Removed)...
> >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.
>
>

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" <(E-Mail Removed)> wrote in message
news:26815D3C-7AD4-4B44-BF5B-(E-Mail Removed)...
> 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" wrote:
>
>> 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" <(E-Mail Removed)> wrote in message
>> news:EF03A1BB-93ED-42A4-831D-(E-Mail Removed)...
>> >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.
>>
>>

Thanks Patrick.

"Patrick Molloy" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:26815D3C-7AD4-4B44-BF5B-(E-Mail Removed)...
> > 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" wrote:
> >
> >> 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" <(E-Mail Removed)> wrote in message
> >> news:EF03A1BB-93ED-42A4-831D-(E-Mail Removed)...
> >> >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.
> >>
> >>