Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to break one row of data into multiple rows, with calcuation
I have inherited a task beyond my excel skills.
I need to turn a single row of data into multiple rows and run a calculation on some of the data. The only problem is I can't do it with a pivot table and can only write simple macros The spreadsheet I need to manipulate is for workforce planning. Resource requirements are entered and are allocated across financial years to one or more projects. If they are on more than one project, the percentage of time they will spend each year on each project is entered. My key column headings are Name, Resource Type, Start Date, Cease Date, Project 1 Name, Project 1 %, Project 2 Name, Project 2 %, . . . ., Project 20 Name, Project 20 %, FY 08/09, FY 9/10, . . . . ., FY 19/20 I have created a simple formula that populates the FY columns with a '1' if the person is working in that year. Example Data: Name: Anna Smith Resource Type: Team Lead Start Date: 01/07/2008 (dd/mm/yyyy format) Cease Date: 31/12/2012 Project 1 Name: Process Project 1 %: 25% Project 2 Name: Training Project 2 %: 75% .. . . . Project 20 Name Project 20 % FY 08/09: 1 (calculates based on the start and cease date) FY 09/10: 1 FY 10/11: 1 FY 11/12: 1 What I want to do is create a macro that will run across my data and change it so each project is represented on a separate line and instead of a "1" in the financial year, the % is displayed (as a number). In my head, the example above would be in two lines and look like this: Row One Name: Anna Smith Resource Type: Team Lead Start Date: 01/07/2008 (dd/mm/yyyy format) Cease Date: 31/12/2012 Project Name: Process Project %: 25% FY 08/09: .25 FY 09/10: .25 FY 10/11: .25 FY 11/12: .25 FY 12/13: 0 Row Two Name: Anna Smith Resource Type: Team Lead Start Date: 01/07/2008 (dd/mm/yyyy format) Cease Date: 31/12/2012 Project Name: Training Project %: 75% FY 08/09: .75 FY 09/10: .75 FY 10/11: .75 FY 11/12: .75 FY 12/13: 0 My raw data is about 1000 lines long and at a maximum would end up as about 5000 lines. If anyone could help me I would be extremely greatful (my macro training just isn't progressing fast enough to help me finish) Thanks in advance |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
To Break Y-Axis for multiple data series | Charts and Charting in Excel | |||
Formula/ Macro to combine data from multiple rows | Excel Programming | |||
Automatically Break Multiple Rows to Two Columns | Excel Discussion (Misc queries) | |||
Combing and Adding Data in Multiple Rows (Macro Needed) | Excel Programming |