Converting Rows of data into Columns [Solved/Closed]

Report
Posts
2
Registration date
Sunday October 20, 2013
Status
Member
Last seen
October 22, 2013
-
Posts
2
Registration date
Sunday October 20, 2013
Status
Member
Last seen
October 22, 2013
-
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

Posts
2669
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
October 15, 2020
446
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
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
1862
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
July 30, 2015
791
not clear what you want.
copy pastespeical transpose ok
Posts
2
Registration date
Sunday October 20, 2013
Status
Member
Last seen
October 22, 2013

Thank you both so much for your responses!!!