Move Column Values to Rows - THANKS in Advance
 

Hey guys and gals,

I took a couple VBA programming classes back when I was in school, but
since I haven't really done anything with it in a few years,
unfortunately, it looks like I've lost much of my Excel acumen.

I've got a massive dataset that has a number of fields for each
company for each year but it's not in a format that I can easily
analyze it or upload into a program to do statistical analysis.

I've got a small sample in Excel of what the data looks like and what
I want it to look like which I'll gladly email you (since I can't
upload it here). But in a nutshell, the data looks like the
following:

Name Location Location Location Location Location
Year 2009 2008 2007
2006 2005
Company 1 Dallas Dallas Houston Houston Houston
Company 2 Chicago Chicago Chicago Dallas Chicago
Company 3 New York New York Los Angeles Los Angeles Los Angeles
Company 4 Boston Boston Boston Boston New York

There are 5 years in my sample. However, after location, there would
then be 5 more columns for another field, and so on, so forth.

I would like it to look like the following:
Name Year Location
Company 1 2009 Dallas
Company 1 2008 Dallas
Company 1 2007 Houston
Company 1 2006 Houston
Company 1 2005 Houston
Company 2 2009 Chicago
Company 2 2008 Chicago
Company 2 2007 Chicago
Company 2 2006 Dallas
Company 2 2005 Chicago
Company 3 2009 New York
Company 3 2008 New York
Company 3 2007 Los Angeles
Company 3 2006 Los Angeles
Company 3 2005 Los Angeles
Company 4 2009 Boston
Company 4 2008 Boston
Company 4 2007 Boston
Company 4 2006 Boston
Company 4 2005 New York

I actually don't think this should be too difficult as I know it would
effectively involve (a) adding 4 new rows for each company, (b) using
the transpose function to move over the years, (c) using the transpose
function to pull over the actual data (of which there are several
hundred different fields - each with 5 years).

I would definitely appreciate your help at your earliest convenience.
And if you want an actual excel file to try out the code, just let me
know and I'll email you.

Thanks in advance!

Move Column Values to Rows - THANKS in Advance
 

On Nov 11, 4:00*pm, chrisnyc wrote:
Hey guys and gals,

I took a couple VBA programming classes back when I was in school, but
since I haven't really done anything with it in a few years,
unfortunately, it looks like I've lost much of my Excel acumen.

I've got a massive dataset that has a number of fields for each
company for each year but it's not in a format that I can easily
analyze it or upload into a program to do statistical analysis.

I've got a small sample in Excel of what the data looks like and what
I want it to look like which I'll gladly email you (since I can't
upload it here). *But in a nutshell, the data looks like the
following:

Name * * * * * *Location * * * *Location * * * *Location * * * *Location * * * *Location
Year * * * * * * * * * *2009 * * * * * *2008 * * * * * * * * * * 2007
2006 * * * * * *2005
Company 1 * * * Dallas *Dallas *Houston Houston Houston
Company 2 * * * Chicago Chicago Chicago Dallas *Chicago
Company 3 * * * New York * * * *New York * * * *Los Angeles * * Los Angeles * * Los Angeles
Company 4 * * * Boston *Boston *Boston *Boston *New York

There are 5 years in my sample. *However, after location, there would
then be 5 more columns for another field, and so on, so forth.

I would like it to look like the following:
Name * *Year * *Location
Company 1 * * * 2009 * *Dallas
Company 1 * * * 2008 * *Dallas
Company 1 * * * 2007 * *Houston
Company 1 * * * 2006 * *Houston
Company 1 * * * 2005 * *Houston
Company 2 * * * 2009 * *Chicago
Company 2 * * * 2008 * *Chicago
Company 2 * * * 2007 * *Chicago
Company 2 * * * 2006 * *Dallas
Company 2 * * * 2005 * *Chicago
Company 3 * * * 2009 * *New York
Company 3 * * * 2008 * *New York
Company 3 * * * 2007 * *Los Angeles
Company 3 * * * 2006 * *Los Angeles
Company 3 * * * 2005 * *Los Angeles
Company 4 * * * 2009 * *Boston
Company 4 * * * 2008 * *Boston
Company 4 * * * 2007 * *Boston
Company 4 * * * 2006 * *Boston
Company 4 * * * 2005 * *New York

I actually don't think this should be too difficult as I know it would
effectively involve (a) adding 4 new rows for each company, (b) using
the transpose function to move over the years, (c) using the transpose
function to pull over the actual data (of which there are several
hundred different fields - each with 5 years).

I would definitely appreciate your help at your earliest convenience.
And if you want an actual excel file to try out the code, just let me
know and I'll email you.

Thanks in advance!

So I don't have to recreate
"If desired, send your file to dguillett @gmail.com I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Move Column Values to Rows - THANKS in Advance
 

Thanks Don - just sent you an email.

On Nov 11, 4:21*pm, Don Guillett Excel MVP
wrote:
On Nov 11, 4:00*pm, chrisnyc wrote:



Hey guys and gals,

I took a couple VBA programming classes back when I was in school, but
since I haven't really done anything with it in a few years,
unfortunately, it looks like I've lost much of my Excel acumen.

I've got a massive dataset that has a number of fields for each
company for each year but it's not in a format that I can easily
analyze it or upload into a program to do statistical analysis.

I've got a small sample in Excel of what the data looks like and what
I want it to look like which I'll gladly email you (since I can't
upload it here). *But in a nutshell, the data looks like the
following:

Name * * * * * *Location * * * *Location * * * *Location * * * *Location * * * *Location
Year * * * * * * * * * *2009 * * * * * *2008 * * * * * * * * * * 2007
2006 * * * * * *2005
Company 1 * * * Dallas *Dallas *Houston Houston Houston
Company 2 * * * Chicago Chicago Chicago Dallas *Chicago
Company 3 * * * New York * * * *New York * * * *Los Angeles * * Los Angeles * * Los Angeles
Company 4 * * * Boston *Boston *Boston *Boston *New York

There are 5 years in my sample. *However, after location, there would
then be 5 more columns for another field, and so on, so forth.

I would like it to look like the following:
Name * *Year * *Location
Company 1 * * * 2009 * *Dallas
Company 1 * * * 2008 * *Dallas
Company 1 * * * 2007 * *Houston
Company 1 * * * 2006 * *Houston
Company 1 * * * 2005 * *Houston
Company 2 * * * 2009 * *Chicago
Company 2 * * * 2008 * *Chicago
Company 2 * * * 2007 * *Chicago
Company 2 * * * 2006 * *Dallas
Company 2 * * * 2005 * *Chicago
Company 3 * * * 2009 * *New York
Company 3 * * * 2008 * *New York
Company 3 * * * 2007 * *Los Angeles
Company 3 * * * 2006 * *Los Angeles
Company 3 * * * 2005 * *Los Angeles
Company 4 * * * 2009 * *Boston
Company 4 * * * 2008 * *Boston
Company 4 * * * 2007 * *Boston
Company 4 * * * 2006 * *Boston
Company 4 * * * 2005 * *New York

I actually don't think this should be too difficult as I know it would
effectively involve (a) adding 4 new rows for each company, (b) using
the transpose function to move over the years, (c) using the transpose
function to pull over the actual data (of which there are several
hundred different fields - each with 5 years).


I would definitely appreciate your help at your earliest convenience.
And if you want an actual excel file to try out the code, just let me
know and I'll email you.

Thanks in advance!

So I don't have to recreate
"If desired, send your file to dguillett I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."

Move Column Values to Rows - THANKS in Advance
 

On Nov 11, 6:07*pm, chrisnyc wrote:
Thanks Don - just sent you an email.

On Nov 11, 4:21*pm, Don Guillett Excel MVP
wrote:



On Nov 11, 4:00*pm, chrisnyc wrote:

Hey guys and gals,

I took a couple VBA programming classes back when I was in school, but
since I haven't really done anything with it in a few years,
unfortunately, it looks like I've lost much of my Excel acumen.

I've got a massive dataset that has a number of fields for each
company for each year but it's not in a format that I can easily
analyze it or upload into a program to do statistical analysis.

I've got a small sample in Excel of what the data looks like and what
I want it to look like which I'll gladly email you (since I can't
upload it here). *But in a nutshell, the data looks like the
following:

Name * * * * * *Location * * * *Location * * * *Location * * * *Location * * * *Location
Year * * * * * * * * * *2009 * * * * * *2008 * * * * * * * * * * 2007
2006 * * * * * *2005
Company 1 * * * Dallas *Dallas *Houston Houston Houston
Company 2 * * * Chicago Chicago Chicago Dallas *Chicago
Company 3 * * * New York * * * *New York * * * *Los Angeles * * Los Angeles * * Los Angeles
Company 4 * * * Boston *Boston *Boston *Boston *New York

There are 5 years in my sample. *However, after location, there would
then be 5 more columns for another field, and so on, so forth.

I would like it to look like the following:
Name * *Year * *Location
Company 1 * * * 2009 * *Dallas
Company 1 * * * 2008 * *Dallas
Company 1 * * * 2007 * *Houston
Company 1 * * * 2006 * *Houston
Company 1 * * * 2005 * *Houston
Company 2 * * * 2009 * *Chicago
Company 2 * * * 2008 * *Chicago
Company 2 * * * 2007 * *Chicago
Company 2 * * * 2006 * *Dallas
Company 2 * * * 2005 * *Chicago
Company 3 * * * 2009 * *New York
Company 3 * * * 2008 * *New York
Company 3 * * * 2007 * *Los Angeles
Company 3 * * * 2006 * *Los Angeles
Company 3 * * * 2005 * *Los Angeles
Company 4 * * * 2009 * *Boston
Company 4 * * * 2008 * *Boston
Company 4 * * * 2007 * *Boston
Company 4 * * * 2006 * *Boston
Company 4 * * * 2005 * *New York

I actually don't think this should be too difficult as I know it would
effectively involve (a) adding 4 new rows for each company, (b) using
the transpose function to move over the years, (c) using the transpose
function to pull over the actual data (of which there are several
hundred different fields - each with 5 years).

I would definitely appreciate your help at your earliest convenience.
And if you want an actual excel file to try out the code, just let me
know and I'll email you.

Thanks in advance!

So I don't have to recreate
"If desired, send your file to dguillett I will only look
if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results."- Hide quoted text -

- Show quoted text -

Sub PARSEDATA()
Application.ScreenUpdating = False
Set ds = Sheets("want")
Set ss = Sheets("Have")
dlr = ds.Cells(ss.Rows.Count, 1).End(xlUp).Row
ds.Cells(2, 1).Resize(1000, 5).ClearContents
dr = 0
For i = 3 To ss.Cells(ss.Rows.Count, 1).End(xlUp).Row
'==========
For j = 2 To 6
With ds
.Cells(j + dr, 1) = ss.Cells(i, 1) 'company
.Cells(j + dr, 3) = ss.Cells(i, j) 'location
.Cells(j + dr, 2) = ss.Cells(2, j) 'year
.Cells(j + dr, 4) = ss.Cells(i, j + 5)
.Cells(j + dr, 5) = ss.Cells(i, j + 10)
End With
Next j
'=========
dr = dr + 5
'MsgBox i
Next i
ds.Columns.AutoFit
Application.ScreenUpdating = True
End Sub