More explanation: Ultimately the end result would be all of the monthly
columns below each row (add 11 rows below each existing row)...then add a
column that is titled "DATA" this would contain the data that is currently
in monthly columns AND a column that contains the MONTH (Jan-Dec for each
existing row). Is there something I can do by bringing it into Access?
Bottom line: Monthly columns...need to be transposed to Monthly ROWS...so,
11 added below each existing row.
Thanks again....

"Dave Peterson" wrote:

What happened when you tried it?

deeds wrote:

Thanks Dave...however, I don't think this will do the trick. I have included
a better example below... Think of it this way....all I want to do is bring
the months down as a row below each Title1,Title2,Title3 combination. So,
ultimately I would have 12 rows of CAT FOOD CANNED combination, 12 rows of
CAT FOOD FRESH combination etc... with 2 new columns labeled MONTH & DATA
example of end result:

Title 1 Title 2 Title 3 Month DATA
CAT FOOD CANNED JAN 5
CAT FOOD CANNED FEB 5
CAT FOOD CANNED MAR 5

Title 1 Title 2 Title 3 Jan Feb Mar Apr May
Cat Food Canned 5 5 5 5 5
Cat Food Fresh 10 10 10 10 10
Cat Drink Water 15 15 15 15 15
Cat Drink Milk 20 20 20 20 20
Dog Food Canned 5 5 5 5 5
Dog Food Fresh 10 10 10 10 10
Dog Drink Water 15 15 15 15 15
Dog Drink Milk 20 20 20 20 20
Rabbit Food Canned 5 5 5 5 5
Rabbit Food Fresh 10 10 10 10 10
Rabbit Drink Water 15 15 15 15 15
Rabbit Drink Milk 20 20 20 20 20

Thanks again for everyone's help!

"Dave Peterson" wrote:

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


--

Dave Peterson