Reformatting data for Pivot Tables

Hassled1

New Member
Joined
Jun 19, 2012
Messages
2
As part of resource management I have a lot of data in tables (Excle 2007) for a particular role. I'm now looking to add some additional reporting to this and would like to use a pivottable however my source table is not in a suitable form for a pivot table.

My data is in the following form
Column headers:
Role, Project, Rate, Week1, week 2, Week 3 etc to Week 52
The first three columns are fairly self explanatory. In the column for each week is the total effort required for that role.

I would like to copy this data from this sheet to a new sheet in the following format
Headings:
Role, Project, Rate, Date, Effort
The date field will contain the header value of Week 1 etc (stored as a date), the effort is the value under that column in the orginal table.

An example is
Role, Project, Rate, 03/06/2012, 10/06/2012, 17/06/2012
Team1, Proj1, 300, 2, 3, 4
Team2, Proj 1. 350, 1.5, 2.5

would map to
Role, Project, Rate, Date, effort
Team1, Proj1, 300, 03/06/2011, 2
Team1, Proj1, 300, 10/06/2011, 2
Team1, Proj1, 300, 17/06/2011, 2
Team2, Proj1, 300, 03/06/2011, 1.5
Team2, Proj1, 300, 03/06/2011, 2.5


I'm struggling to set this up using vba, any help would be appreciated.

Thanks
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

Watch MrExcel Video

Forum statistics

Threads
1,122,463
Messages
5,596,284
Members
414,051
Latest member
tabecker

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
Top