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
chuaf24 Posts 2 Registration date Sunday October 20, 2013 Status Member Last seen October 22, 2013 - Oct 22, 2013 at 11:30 AM
Related:
- Converting Rows of data into Columns
- Display two columns in data validation list but return only one - Guide
- Tmobile data check - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- How to convert number into words in ms word in shortcut key - Guide
- Date of birth in words converter ✓ - Word Forum
3 responses
TrowaD
Posts
2921
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
December 27, 2022
552
Oct 22, 2013 at 11:20 AM
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):
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:
5) Completed header on Sheet2 by selecting B1:C1 and dragging it as far as needed to the right.
Best regards,
Trowa
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
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Oct 20, 2013 at 10:39 PM
Oct 20, 2013 at 10:39 PM
not clear what you want.
copy pastespeical transpose ok
copy pastespeical transpose ok
chuaf24
Posts
2
Registration date
Sunday October 20, 2013
Status
Member
Last seen
October 22, 2013
Oct 22, 2013 at 11:30 AM
Oct 22, 2013 at 11:30 AM
Thank you both so much for your responses!!!
