Hi,

Not sure if this is the correct group or not.
I need to massage an Excel spreadsheet.
Specifically, if a cell in a column contains
more than one element (separated by commas)
then I need to duplicate that row with one
element in each row. For example, I want to
convert this:
Stuff1 an element
Stuff2 one element, two element
Stuff3 another element

into this:
Stuff1 an element
Stuff2 one element
Stuff2 two element
Stuff3 another element

where "Stuff1" is in column 1, and
"an element" is in column 2.

Any ideas how I can do this? I can program
in more languages than I care to remember,
but have never used Visual Basic. Would
Visual Basic be able to do this, and where
might I find an example of something similar.

The spreadsheet has thousands of rows with
hundreds of rows which need to be duplicated.
Doing it by hand would therefore be a last
resort.

Many thanks for any help you can give.
--
Regards,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/martin_leese/

Some ideas...
Use the TextToColumns method and separate the text into
columns on a worksheet (using a space as the delimiter).
Then loop thru each row and check for those rows with more than two
columns of data.
Insert the appropriate number of extra rows and copy the extra
column data into the new rows. (then delete the extra columns)

Another way might be to use the Split function on each cell.
It returns a zero based array containing strings that were separated by a
specified delimiter.
If the upper bound of the array is greater than one then insert
additional row(s) into the worksheet and copy the appropriate array
elements to the new row(s).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Martin Leese"
wrote in message
Hi,
Not sure if this is the correct group or not.
I need to massage an Excel spreadsheet.
Specifically, if a cell in a column contains
more than one element (separated by commas)
then I need to duplicate that row with one
element in each row. For example, I want to
convert this:
Stuff1 an element
Stuff2 one element, two element
Stuff3 another element

into this:
Stuff1 an element
Stuff2 one element
Stuff2 two element
Stuff3 another element

where "Stuff1" is in column 1, and
"an element" is in column 2.

Any ideas how I can do this? I can program
in more languages than I care to remember,
but have never used Visual Basic. Would
Visual Basic be able to do this, and where
might I find an example of something similar.

The spreadsheet has thousands of rows with
hundreds of rows which need to be duplicated.
Doing it by hand would therefore be a last
resort.

Many thanks for any help you can give.
--
Regards,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/martin_leese/

finally I'm finished <g

that's not a neat one (and may be a bit slow) but try:

On Error Resume Next
For i = 1 To Selection.Cells.Count + 1
If Application.WorksheetFunction.Find(",", ActiveCell.Value) Then
ActiveCell.Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Cut
ActiveCell.Offset(1, 0).Select
ActiveSheet.Paste
ActiveCell.Offset(-1, 0).Value = Left(ActiveCell,
Application.WorksheetFunction.Find(",", ActiveCell) - 1)
ActiveCell.Value = Right(ActiveCell, Len(ActiveCell) -
Application.WorksheetFunction.Find(",", ActiveCell))
Range(Selection.Offset(1, 0), Selection.End(xlDown)).Select
End If
Next i

Application.CutCopyMode = False

End Sub


HIH

forgot to mention that you need to select range with "elements"
sorry

try my macro on a copy of original dataset

cos it doesn't check whether there is enough rows "benneath" the data
to perform the operation - the data might be overwritten

Try a macro below. This would put the result into new worksheet after
Activesheet, assuming Stuff1 start at A1 and elements start B1.

Sub mytest()
Dim stuff As Range, dst As Range
Dim rng As Range
Dim tmp, k As Long

Set stuff = Range(Range("A1"), Range("A1").End(xlDown))
Set dst = Worksheets.Add(after:=ActiveSheet).Cells(1, "A")

On Error Resume Next
For Each rng In stuff
tmp = Split(rng.Offset(0, 1), ",")
k = IIf(UBound(tmp) = 0, UBound(tmp) + 1, 1)
dst.Resize(k, 1) = rng.Value
dst.Resize(k, 1).Offset(0, 1) = Application.Transpose(tmp)
Set dst = dst.Offset(k, 0)
Next
End Sub

keiji

"Martin Leese" wrote in message
news:i5ZFj.122893$C61.30041@edtnps89...
Hi,

Not sure if this is the correct group or not.
I need to massage an Excel spreadsheet.
Specifically, if a cell in a column contains
more than one element (separated by commas)
then I need to duplicate that row with one
element in each row. For example, I want to
convert this:
Stuff1 an element
Stuff2 one element, two element
Stuff3 another element

into this:
Stuff1 an element
Stuff2 one element
Stuff2 two element
Stuff3 another element

where "Stuff1" is in column 1, and
"an element" is in column 2.

Any ideas how I can do this? I can program
in more languages than I care to remember,
but have never used Visual Basic. Would
Visual Basic be able to do this, and where
might I find an example of something similar.

The spreadsheet has thousands of rows with
hundreds of rows which need to be duplicated.
Doing it by hand would therefore be a last
resort.

Many thanks for any help you can give.
--
Regards,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/martin_leese/

kounoike wrote:
Try a macro below. This would put the result into new worksheet after
Activesheet, assuming Stuff1 start at A1 and elements start B1.
....

Many thanks to kounoike and everyone else
who responded. It worked like a dream. I
wish there were more newsgroups as helpful
as this one.

--
Regards,
Martin Leese
E-mail: LID
Web: http://members.tripod.com/martin_leese/