Performing Data Transformations
 

On Sheet1 I have the following data:

Column A = Phase
Column B = Activity
Column C = Jan 2007 €“ Mgmt. Hours
Column D = Jan 2007 €“ Non-Mgmt. Hours
Column E = Feb 2007 €“ Mgmt. Hours
Column F = Feb 2007 €“ Non-Mgmt. Hours
€¦
Column Z = Dec 2007 €“ Non-Mgmt. Hours

On Sheet2, I need to €œtransform€� the data from Sheet1 as follows:

Column A = Phase
Column B = Activity
Column C = M (for Mgmt.) or N (for Non-Mgmt.)
Column D = Year
Column E = Month

In case you may be wondering, Sheet1 is structured in the manner that it is,
due to the need to create a facsimile of a paper form that users are already
familiar/comfortable with.

Is anyone aware of any built-in Excel function(s) (or €œmechanisms€�) that can
help me automate the data transformation process? (Note: for every row in
Sheet1, as many as 24 rows will be created in Sheet2.) If the only solution
is a macro, can someone point me to any existing code snippets that I can use
as a starting point (since I am a novice at writing VBA code)?

Thanks in advance for any help/guidance.

Performing Data Transformations
 

Column F on Sheet2 = Hours (sorry for the omission)


"Bob" wrote:

On Sheet1 I have the following data:

Column A = Phase
Column B = Activity
Column C = Jan 2007 €“ Mgmt. Hours
Column D = Jan 2007 €“ Non-Mgmt. Hours
Column E = Feb 2007 €“ Mgmt. Hours
Column F = Feb 2007 €“ Non-Mgmt. Hours
€¦
Column Z = Dec 2007 €“ Non-Mgmt. Hours

On Sheet2, I need to €œtransform€� the data from Sheet1 as follows:

Column A = Phase
Column B = Activity
Column C = M (for Mgmt.) or N (for Non-Mgmt.)
Column D = Year
Column E = Month

In case you may be wondering, Sheet1 is structured in the manner that it is,
due to the need to create a facsimile of a paper form that users are already
familiar/comfortable with.

Is anyone aware of any built-in Excel function(s) (or €œmechanisms€�) that can
help me automate the data transformation process? (Note: for every row in
Sheet1, as many as 24 rows will be created in Sheet2.) If the only solution
is a macro, can someone point me to any existing code snippets that I can use
as a starting point (since I am a novice at writing VBA code)?

Thanks in advance for any help/guidance.

Performing Data Transformations
 

On Sheet2, Column F = Hours (sorry for the omission)


"Bob" wrote:

On Sheet1 I have the following data:

Column A = Phase
Column B = Activity
Column C = Jan 2007 €“ Mgmt. Hours
Column D = Jan 2007 €“ Non-Mgmt. Hours
Column E = Feb 2007 €“ Mgmt. Hours
Column F = Feb 2007 €“ Non-Mgmt. Hours
€¦
Column Z = Dec 2007 €“ Non-Mgmt. Hours

On Sheet2, I need to €œtransform€� the data from Sheet1 as follows:

Column A = Phase
Column B = Activity
Column C = M (for Mgmt.) or N (for Non-Mgmt.)
Column D = Year
Column E = Month

In case you may be wondering, Sheet1 is structured in the manner that it is,
due to the need to create a facsimile of a paper form that users are already
familiar/comfortable with.

Is anyone aware of any built-in Excel function(s) (or €œmechanisms€�) that can
help me automate the data transformation process? (Note: for every row in
Sheet1, as many as 24 rows will be created in Sheet2.) If the only solution
is a macro, can someone point me to any existing code snippets that I can use
as a starting point (since I am a novice at writing VBA code)?

Thanks in advance for any help/guidance.