Splitting records
 

Hi everybody,

I need some help to enable me working smarter and faster. I have a list
consisting of three columns which I need to convert in multiple rows as
follows.

A B C D
Tommy 500 75 575
Peter 100 15 115
Jane 800 120 920
Greg 200 30 230

The list should look as follows as it requires further processing

Tommy 500
Tommy 75
Tommy 575
Peter 100
Peter 15
Peter 115
Jane 800
Jane 120
Jane 920

The list may vary in number of lines.

Thank you for your guidance.

Splitting records
 

This assume the original data is in Sheet1 and the output will be put in
Sheet2:

Sub settupp()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
namee = Cells(i, 1).Value
For j = 2 To 4
numberr = Cells(i, j).Value
s2.Cells(k, 1) = namee
s2.Cells(k, 2) = numberr
k = k + 1
Next
Next
End Sub

Adjust the names to suit your needs.
--
Gary''s Student - gsnu200836


"wins007" wrote:

Hi everybody,

I need some help to enable me working smarter and faster. I have a list
consisting of three columns which I need to convert in multiple rows as
follows.

A B C D
Tommy 500 75 575
Peter 100 15 115
Jane 800 120 920
Greg 200 30 230

The list should look as follows as it requires further processing

Tommy 500
Tommy 75
Tommy 575
Peter 100
Peter 15
Peter 115
Jane 800
Jane 120
Jane 920

The list may vary in number of lines.

Thank you for your guidance.

Splitting records
 

Thank you mate, it works perfectly

"Gary''s Student" wrote:

This assume the original data is in Sheet1 and the output will be put in
Sheet2:

Sub settupp()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
namee = Cells(i, 1).Value
For j = 2 To 4
numberr = Cells(i, j).Value
s2.Cells(k, 1) = namee
s2.Cells(k, 2) = numberr
k = k + 1
Next
Next
End Sub

Adjust the names to suit your needs.
--
Gary''s Student - gsnu200836


"wins007" wrote:

Hi everybody,

I need some help to enable me working smarter and faster. I have a list
consisting of three columns which I need to convert in multiple rows as
follows.

A B C D
Tommy 500 75 575
Peter 100 15 115
Jane 800 120 920
Greg 200 30 230

The list should look as follows as it requires further processing

Tommy 500
Tommy 75
Tommy 575
Peter 100
Peter 15
Peter 115
Jane 800
Jane 120
Jane 920

The list may vary in number of lines.

Thank you for your guidance.

Splitting records
 

This code will Let you go back again:
Sub newlist()
Set w1 = Sheets("Sheet1")
Set w2 = Sheets("Sheet2")
w2.Cells(1, 1).Value = w1.Cells(1, 1).Value
w2.Cells(1, 2).Value = w1.Cells(1, 2).Value
Ide = Cells(1, 1).Value
w1.Activate
n = Cells(Rows.count, 1).End(xlUp).Row
k = 3
kk = 1
For i = 2 To n
If w1.Cells(i, 1).Value = Ide Then
w2.Cells(kk, k).Value = w1.Cells(i, 2).Value
k = k + 1
Else
kk = kk + 1
k = 3
Ide = w1.Cells(i, 1).Value
w2.Cells(kk, 1).Value = Ide
w2.Cells(kk, 2).Value = w1.Cells(i, 2).Value
End If
Next
End Sub


Regards,
Ryan---
--
RyGuy


"wins007" wrote:

Thank you mate, it works perfectly

"Gary''s Student" wrote:

This assume the original data is in Sheet1 and the output will be put in
Sheet2:

Sub settupp()
Set s1 = Sheets("Sheet1")
Set s2 = Sheets("Sheet2")
s1.Activate
n = Cells(Rows.Count, 1).End(xlUp).Row
k = 1
For i = 1 To n
namee = Cells(i, 1).Value
For j = 2 To 4
numberr = Cells(i, j).Value
s2.Cells(k, 1) = namee
s2.Cells(k, 2) = numberr
k = k + 1
Next
Next
End Sub

Adjust the names to suit your needs.
--
Gary''s Student - gsnu200836


"wins007" wrote:

Hi everybody,

I need some help to enable me working smarter and faster. I have a list
consisting of three columns which I need to convert in multiple rows as
follows.

A B C D
Tommy 500 75 575
Peter 100 15 115
Jane 800 120 920
Greg 200 30 230

The list should look as follows as it requires further processing

Tommy 500
Tommy 75
Tommy 575
Peter 100
Peter 15
Peter 115
Jane 800
Jane 120
Jane 920

The list may vary in number of lines.

Thank you for your guidance.