Timespread data transpose

E

eschwanz

New Member
Joined
Jun 7, 2012
Messages
6
Hello,

I am looking for some assistance in writing a macro to transpose timespread data that is coming out of a program that I use. I need to be able to make a calculation on the data, then put it back into a pivot table to spread it again by period. I only need help getting the data from timespread to data table. See example below. The columns 1 and 2 need to be repeated for every period. The periods, will be changing every time i run the report.

Thank you for your help.

[TABLE="class: grid, width: 691"]
<colgroup><col><col><col><col><col><col span="4"><col></colgroup><tbody>[TR]
[TD]From this[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Resource[/TD]
[TD]Field[/TD]
[TD="align: right"]01-Dec[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]01-May[/TD]
[TD="align: right"]01-Jun[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD]Hours[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD]Hours[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD]Hours[/TD]
[TD] [/TD]
[TD] [/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD]Hours[/TD]
[TD] [/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]50[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD]Hours[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]10[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD]Hours[/TD]
[TD] [/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="class: grid, width: 289"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Into this[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Project[/TD]
[TD]Resource[/TD]
[TD]Period[/TD]
[TD]Hours[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-Dec[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-May[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 1[/TD]
[TD="align: right"]01-Jun[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-May[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job A[/TD]
[TD]Resource 2[/TD]
[TD="align: right"]01-Jun[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-Jan[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-May[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 3[/TD]
[TD="align: right"]01-Jun[/TD]
[TD="align: right"]20[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-May[/TD]
[TD="align: right"]50[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 4[/TD]
[TD="align: right"]01-Jun[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-Dec[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-May[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job C[/TD]
[TD]Resource 5[/TD]
[TD="align: right"]01-Jun[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-Dec[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-Jan[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-Feb[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-Mar[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-Apr[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-May[/TD]
[TD="align: right"]30[/TD]
[/TR]
[TR]
[TD]Job B[/TD]
[TD]Resource 6[/TD]
[TD="align: right"]01-Jun[/TD]
[TD="align: right"]30[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Try this for results on sheet2.
Code: 
[COLOR="Navy"]Sub[/COLOR] MG07Dec23
[COLOR="Navy"]Dim[/COLOR] Ray [COLOR="Navy"]As[/COLOR] Variant, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
Ray = ActiveSheet.Cells(1).CurrentRegion
ReDim nray(1 To UBound(Ray, 1) * UBound(Ray, 2), 1 To 4)
nray(1, 1) = "project": nray(1, 2) = "Resource"
nray(1, 3) = "Periord": nray(1, 4) = "Hour"
c = 1
[COLOR="Navy"]For[/COLOR] n = 2 To UBound(Ray, 1)
    [COLOR="Navy"]For[/COLOR] Ac = 4 To UBound(Ray, 2)
        c = c + 1
        nray(c, 1) = Ray(n, 1): nray(c, 2) = Ray(n, 2)
        nray(c, 3) = Format(Ray(1, Ac), "dd-mmm"): nray(c, 4) = Ray(n, Ac)
    [COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]Next[/COLOR] n
[COLOR="Navy"]With[/COLOR] Sheets("Sheet2").Range("A1").Resize(c, 4)
    .Value = nray
    .Borders.Weight = 2
    .Columns.AutoFit
 [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Regards Mick
 
Upvote 0
Thanks Mick. This worked perfectly. In the last lines, I am having this feed into a pivot table, what do i need to have the data on sheet2 formatted as a table or dumped into a table? So i dont have to change the data source of the pivot table to captured new rows in the original data set.
 
Last edited:
Upvote 0
If you change the last few lines as below it should convert the results data in sheet2 to a "Table".
Code: 
With Sheets("Sheet2").Range("A1").Resize(c, 4)
   .Value = nray
   .Columns.AutoFit
End With
Sheets("Sheet2").ListObjects.Add(xlSrcRange, Sheets("Sheet2").Range("A1").Resize(c, 4), , xlYes).Name = "MyTable"
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

W
  • Solved
Validate month based on contract dates & Calculate number of weeks in that month to calculate hours booked per month
2
Replies
16
Views
2K
phiero21
P
T
  • Solved
How can I filter duplicates from a worksheet column out of a data validation list drop-down box?
Replies
5
Views
241
Peter_SSs
Peter_SSs
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
761
Fluff
Fluff
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
59
Skyybot
Skyybot
M
  • Solved
How to count certain month from a column
Replies
3
Views
395
Joe4
Joe4

Forum statistics

Threads
1,224,823
Messages
6,181,183
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top