Converting Rows of data into Columns

Solved/Closed
chuaf24 Posts 2 Registration date Sunday October 20, 2013 Status Member Last seen October 22, 2013 - Oct 20, 2013 at 12:57 AM
chuaf24 Posts 2 Registration date Sunday October 20, 2013 Status Member Last seen October 22, 2013 - Oct 22, 2013 at 11:30 AM
Hi everyone,

I hope you all can help. I have a file in with 17,000 lines of data in rows that I need converted into columns. I don't work with Excel very often so I am hoping someone can walk me through this.

My data looks like this:

Study ID Date of DX Dx
3 1/1/2011 123.45
3 2/2/2011 408.19
3 3/2/2011 302.46

What I need is for all the row data converted into columns so that all the DX are in columns by ID.

I need it to look like:

Study ID Date1 Dx1 Date2 Dx2 Date3 Dx3
3 1/1/2011 123.45 2/2/2011 408.19 3/2/2011 302.46


Any help is much appreciated.....

3 replies

TrowaD Posts 2913 Registration date Sunday September 12, 2010 Status Moderator Last seen November 21, 2022 541
Oct 22, 2013 at 11:20 AM
Hi Chuaf,

This is what I did:
1) Paste following sample data on the first sheet which populated range A1:C6 (I added 2 rows with a different Study ID):
Study ID	Date of DX	Dx
3 1-1-2011 123.45
3 2-2-2011 408.19
3 3-2-2011 302.46
4 4-1-2013 199.44
4 5-1-2013 155.76

2) Added sheet and named it Sheet2.

3) Added header on Sheet2 which took range A1:C1: Study ID - Date1 - Dx1.

4) Run The following code:
Sub RunMe()
Dim lRow, lRow2, lCol As Integer
lRow = Range("A1").End(xlDown).Row
Range("A2:C2").Copy Sheets("Sheet2").Range("A2")
For Each cell In Range("A3:A" & lRow)
If cell.Value = cell.Offset(-1, 0) Then
lRow2 = Sheets("Sheet2").Range("A1").End(xlDown).Row
lCol = Sheets("Sheet2").Cells(lRow2, Columns.Count).End(xlToLeft).Column + 1
Range(cell.Offset(0, 1), cell.Offset(0, 2)).Copy _
Sheets("Sheet2").Cells(lRow2, lCol)
Else
lRow2 = Sheets("Sheet2").Range("A1").End(xlDown).Row + 1
Range(cell, cell.Offset(0, 2)).Copy Sheets("Sheet2").Range("A" & lRow2)
End If
Next cell
End Sub

5) Completed header on Sheet2 by selecting B1:C1 and dragging it as far as needed to the right.

Best regards,
Trowa
1
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Oct 20, 2013 at 10:39 PM
not clear what you want.
copy pastespeical transpose ok
0
chuaf24 Posts 2 Registration date Sunday October 20, 2013 Status Member Last seen October 22, 2013
Oct 22, 2013 at 11:30 AM
Thank you both so much for your responses!!!
0