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

Best to place your macro in the question:

Idiot way to do that:

- Place Value in a variable
- Break the value based on your required length

i.e. Variable A="My test data is here "
Start=1
While mid(VariableA, Start, Length)=
Debug.print Mid(VariableA, Start, Length)
Start = Start + Length
wend

Ok this is not very ideal soltuion.
--
Malik


"KatJ" wrote:

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