Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting data into multiple rows
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting data into multiple rows
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting data into multiple rows
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Converting data into multiple rows
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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Converting multiple data codes to numbers | Excel Discussion (Misc queries) | |||
Converting multiple rows of Excel data into Word Document | Excel Discussion (Misc queries) | |||
Converting multiple rows into columns | Excel Discussion (Misc queries) | |||
converting data in colums to rows | Excel Discussion (Misc queries) | |||
Need help with Converting Rows of data into 2 columns | Excel Worksheet Functions |