software

Hi
I hope someone can help me.
i'll try and illustrate what I mean, as im fairly new to all of this,
so do not really know the technical 'language' to explain what im
hoping to do.

Column D, in this case being my problem:
Where in each cell there are several pieces of data that are seperated
by commas. I want to be able to split the data at the comma, but
instead of the data continuing across the page(transpose??) so that
each 'piece' of data has its own cell going across in a row, I want
each piece of data to form a new row under the original, and also copy
what was originally in cells A - C

Sheet currently looks like this..........

A B C D

1) JANE 1 YES 123, 45, 789
2) PAUL 2 YES 101, 11
3) CHRIS 3 NO 124, 9999,
5697, 88, 587so ideally the sheet would end up looking like this.........

A B C D

1) JANE 1 YES 123
2) JANE 1 YES 45
3) JANE 1 YES 789
4) PAUL 2 YES 101
5) PAUL 2 YES 11
6) CHRIS 3 NO 124
7) CHRIS 3 NO 9999

and so on......

Can this be done? if so any help or advice will be greatly appreciated
The sheet I have to work this on is huge

thanks in advance!Hi,

Try this. Data is copied from Sheet1 to Sheet2. Copy code into a general
module.

Sub transform()

Dim v As Variant
Dim ws1 As Worksheet, ws2 As Worksheet
Dim lastrow As Long, r As Long
Dim i As Integer

Set ws1 = Worksheets(quot;sheet1quot;) ' lt;=== change sheet names as required
Set ws2 = Worksheets(quot;sheet2quot;)

With ws1
lastrow = .Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
For r = 2 To lastrow 'lt;=== assumes data starts in row 2
v = Split(.Cells(r, quot;Dquot;), quot;,quot;)
For i = LBound(v) To UBound(v)
.Range(quot;aquot; amp; r amp; quot;:cquot; amp; r).Copy ws2.Cells(Rows.Count,
quot;Aquot;).End(xlUp)(2)
ws2.Cells(ws2.Cells(Rows.Count, quot;Aquot;).End(xlUp).Row, quot;Dquot;) = v(i)
Next i
Next r
End With

End SubHTH

quot; wrote:

gt; Hi
gt; I hope someone can help me.
gt; i'll try and illustrate what I mean, as im fairly new to all of this,
gt; so do not really know the technical 'language' to explain what im
gt; hoping to do.
gt;
gt; Column D, in this case being my problem:
gt; Where in each cell there are several pieces of data that are seperated
gt; by commas. I want to be able to split the data at the comma, but
gt; instead of the data continuing across the page(transpose??) so that
gt; each 'piece' of data has its own cell going across in a row, I want
gt; each piece of data to form a new row under the original, and also copy
gt; what was originally in cells A - C
gt;
gt; Sheet currently looks like this..........
gt;
gt; A B C D
gt;
gt; 1) JANE 1 YES 123, 45, 789
gt; 2) PAUL 2 YES 101, 11
gt; 3) CHRIS 3 NO 124, 9999,
gt; 5697, 88, 587
gt;
gt;
gt; so ideally the sheet would end up looking like this.........
gt;
gt; A B C D
gt;
gt; 1) JANE 1 YES 123
gt; 2) JANE 1 YES 45
gt; 3) JANE 1 YES 789
gt; 4) PAUL 2 YES 101
gt; 5) PAUL 2 YES 11
gt; 6) CHRIS 3 NO 124
gt; 7) CHRIS 3 NO 9999
gt;
gt; and so on......
gt;
gt; Can this be done? if so any help or advice will be greatly appreciated
gt; The sheet I have to work this on is huge
gt;
gt; thanks in advance!
gt;
gt;

Toppers

Many thanks for your time, its worked Toppers wrote:
gt; Hi,
gt;
gt; Try this. Data is copied from Sheet1 to Sheet2. Copy code into a general
gt; module.
gt;
gt; Sub transform()
gt;
gt; Dim v As Variant
gt; Dim ws1 As Worksheet, ws2 As Worksheet
gt; Dim lastrow As Long, r As Long
gt; Dim i As Integer
gt;
gt; Set ws1 = Worksheets(quot;sheet1quot;) ' lt;=== change sheet names as required
gt; Set ws2 = Worksheets(quot;sheet2quot;)
gt;
gt; With ws1
gt; lastrow = .Cells(Rows.Count, quot;Aquot;).End(xlUp).Row
gt; For r = 2 To lastrow 'lt;=== assumes data starts in row 2
gt; v = Split(.Cells(r, quot;Dquot;), quot;,quot;)
gt; For i = LBound(v) To UBound(v)
gt; .Range(quot;aquot; amp; r amp; quot;:cquot; amp; r).Copy ws2.Cells(Rows.Count,
gt; quot;Aquot;).End(xlUp)(2)
gt; ws2.Cells(ws2.Cells(Rows.Count, quot;Aquot;).End(xlUp).Row, quot;Dquot;) = v(i)
gt; Next i
gt; Next r
gt; End With
gt;
gt; End Sub
gt;
gt;
gt; HTH
gt;
gt; quot; wrote:
gt;
gt; gt; Hi
gt; gt; I hope someone can help me.
gt; gt; i'll try and illustrate what I mean, as im fairly new to all of this,
gt; gt; so do not really know the technical 'language' to explain what im
gt; gt; hoping to do.
gt; gt;
gt; gt; Column D, in this case being my problem:
gt; gt; Where in each cell there are several pieces of data that are seperated
gt; gt; by commas. I want to be able to split the data at the comma, but
gt; gt; instead of the data continuing across the page(transpose??) so that
gt; gt; each 'piece' of data has its own cell going across in a row, I want
gt; gt; each piece of data to form a new row under the original, and also copy
gt; gt; what was originally in cells A - C
gt; gt;
gt; gt; Sheet currently looks like this..........
gt; gt;
gt; gt; A B C D
gt; gt;
gt; gt; 1) JANE 1 YES 123, 45, 789
gt; gt; 2) PAUL 2 YES 101, 11
gt; gt; 3) CHRIS 3 NO 124, 9999,
gt; gt; 5697, 88, 587
gt; gt;
gt; gt;
gt; gt; so ideally the sheet would end up looking like this.........
gt; gt;
gt; gt; A B C D
gt; gt;
gt; gt; 1) JANE 1 YES 123
gt; gt; 2) JANE 1 YES 45
gt; gt; 3) JANE 1 YES 789
gt; gt; 4) PAUL 2 YES 101
gt; gt; 5) PAUL 2 YES 11
gt; gt; 6) CHRIS 3 NO 124
gt; gt; 7) CHRIS 3 NO 9999
gt; gt;
gt; gt; and so on......
gt; gt;
gt; gt; Can this be done? if so any help or advice will be greatly appreciated
gt; gt; The sheet I have to work this on is huge
gt; gt;
gt; gt; thanks in advance!
gt; gt;
gt; gt;