Move Column Values to Rows - THANKS in Advance

[chrisnyc]

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!

 

[Don Guillett Excel MVP]

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        LosAngeles     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."

 

[chrisnyc]

Thanks Don - just sent you an email.

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."

 

[Don Guillett Excel MVP]

Thanks Don - just sent you an email.




- 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