You could use a little macro:
Option Explicit
Sub testme01()
Dim CurWks As Worksheet
Dim NewWks As Worksheet
Dim oRow As Long
Dim iCol As Long
Dim FirstRow As Long
Dim LastRow As Long
Dim iRow As Long
Dim FirstCol As Long
Set CurWks = Worksheets("sheet1")
Set NewWks = Worksheets.Add
NewWks.Range("a1").Resize(1, 5).Value _
= Array("title1", "title2", "title3", "month", "data")
oRow = 2
With CurWks
FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
FirstCol = 4
For iRow = FirstRow To LastRow
For iCol = FirstCol To _
.Cells(iRow, .Columns.Count).End(xlToLeft).Column
NewWks.Cells(oRow, "A").Value = .Cells(1, "A").Value
NewWks.Cells(oRow, "B").Value = .Cells(1, "B").Value
NewWks.Cells(oRow, "C").Value = .Cells(1, "C").Value
NewWks.Cells(oRow, "D").Value = .Cells(1, iCol).Value
NewWks.Cells(oRow, "E").Value = .Cells(iRow, iCol).Value
oRow = oRow + 1
Next iCol
Next iRow
End With
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
deeds wrote:
Here is what I have:
Title Title Title Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
A B C 5 6 7 8 9 10 11 12 13
14 15 16
I want to bring the months and the data down as rows underneath the ABC
group, now I do have about 1500 rows like this. So, ultimately I need to add
12 rows underneath each current row, add the months in a column and add the
data for the corresponding month in a new column titled "data".
Let me know if you need more information...THANKS!
--
Dave Peterson