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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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
77
Peter_SSs
Peter_SSs
A
  • Solved
AVERAGEIFS with 4 criteria
2
Replies
16
Views
750
Fluff
Fluff
H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
M
  • Solved
How to count certain month from a column
Replies
3
Views
388
Joe4
Joe4

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

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