Macro to traspose data
Solved/Closed
aac1975
Posts
2
Registration date
Sunday 13 October 2013
Status
Member
Last seen
14 October 2013
-
13 Oct 2013 à 23:13
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 21 Oct 2013 à 12:05
TrowaD Posts 2921 Registration date Sunday 12 September 2010 Status Contributor Last seen 27 December 2022 - 21 Oct 2013 à 12:05
Related:
- Macro to traspose data
- Tmobile data check - Guide
- How to copy data from one excel sheet to another - Guide
- Data transmission cables - Guide
- Blancco data cleaner - Download - Cleaning and optimization
- How to reinstall windows 10 without losing data - Guide
4 responses
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
14 Oct 2013 à 10:53
14 Oct 2013 à 10:53
Hi AAC,
This is what I did:
- Pasted your sample data on Sheet1 which covered the cells A1:H11.
- Added sheet and named it Sheet2.
- Added header to Sheet2 (ID, Day, Working hour) which covered the cells A1:C1.
Then I run the following code:
Best regards,
Trowa
This is what I did:
- Pasted your sample data on Sheet1 which covered the cells A1:H11.
- Added sheet and named it Sheet2.
- Added header to Sheet2 (ID, Day, Working hour) which covered the cells A1:C1.
Then I run the following code:
Sub RunMe()
Dim lRow, x, y As Integer
Sheets("Sheet1").Select
lRow = Range("B2").End(xlDown).Row
For Each cell In Range("B2:B" & lRow)
Range(cell, cell.Offset(0, 6)).Copy
Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
Next cell
Sheets("Sheet2").Select
lRow = Range("C" & Rows.Count).End(xlUp).Row
For x = 2 To lRow Step 7
y = y + 1
Range("A" & x) = y
Next x
For Each cell In Range("A2:A" & lRow)
If cell.Value <> vbNullString Then cell.Copy _
Else: cell.PasteSpecial
Next cell
x = 2
Do
Range(Cells(x, "B"), Cells(x + 6, "B")).Value = Application.Transpose(Array(1, 2, 3, 4, 5, 6, 7))
x = x + 7
Loop Until x > lRow
Application.CutCopyMode = False
End Sub
Best regards,
Trowa
aac1975
Posts
2
Registration date
Sunday 13 October 2013
Status
Member
Last seen
14 October 2013
14 Oct 2013 à 19:34
14 Oct 2013 à 19:34
Thanks a lot.
Hi
Sorry I am still unable to do it.
In my actual dataset, there is 35 days data (In example I sent you it was for 7 days). Then number of IDs are 1669 (In example I sent there were 10 participants). Can you please modify macro for me, considering that there are 35 days instead of 10 and 1669 participants instead of 10.
AAC
Sorry I am still unable to do it.
In my actual dataset, there is 35 days data (In example I sent you it was for 7 days). Then number of IDs are 1669 (In example I sent there were 10 participants). Can you please modify macro for me, considering that there are 35 days instead of 10 and 1669 participants instead of 10.
AAC
TrowaD
Posts
2921
Registration date
Sunday 12 September 2010
Status
Contributor
Last seen
27 December 2022
555
21 Oct 2013 à 12:05
21 Oct 2013 à 12:05
Hi AAC,
The code doesn't mind you adding more ID's, adding days is the issue.
Here you go:
Best regards,
Trowa
The code doesn't mind you adding more ID's, adding days is the issue.
Here you go:
Sub RunMe()
Dim lRow, x, y As Integer
Sheets("Sheet1").Select
lRow = Range("B2").End(xlDown).Row
For Each cell In Range("B2:B" & lRow)
Range(cell, cell.Offset(0, 34)).Copy
Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
Next cell
Sheets("Sheet2").Select
lRow = Range("C" & Rows.Count).End(xlUp).Row
For x = 2 To lRow Step 35
y = y + 1
Range("A" & x) = y
Next x
For Each cell In Range("A2:A" & lRow)
If cell.Value <> vbNullString Then cell.Copy _
Else: cell.PasteSpecial
Next cell
x = 2
Do
Range(Cells(x, "B"), Cells(x + 34, "B")).Value = _
Application.Transpose(Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35))
x = x + 35
Loop Until x > lRow
Application.CutCopyMode = False
End Sub
Best regards,
Trowa
17 Oct 2013 à 19:17
Sorry I am still unable to do it.
In my actual dataset, there is 35 days data (In example I sent you it was for 7 days). Then number of IDs are 1669 (In example I sent there were 10 participants). Can you please modify macro for me, considering that there are 35 days instead of 10 and 1669 participants instead of 10.
AAC