Convert data for Pivot Table

[teeitup]

I currently have a set of data in a traditional budget layout.

With columns:

Primary, Group, Sub-Group, and dates: Jan Feb March Etc. (each in its own column)

Listed under Primary, Group and Sub-Group is information about the type of expense.

Listed under each month is the amount for the given month.

So each row contains many different months of data located in many columns

What I would like to do is convert the data so it can more effectively used in a pivot table.

With Columns:

Primary, Group, Sub-Group, Month, Amount

In this layout instead of having months in many columns it would simply list the month in one common column. So each row would contain only one amount.

I've racked my brain, but can't think of an easy way to make this conversion.

Thanks for the help,

Doug

[Bob Phillips]

Public Sub ProcessData()
Dim i As Long, j As Long
Dim LastRow As Long

With Application

.ScreenUpdating = False
.Calculation = xlCalculationManual
End With

With ActiveSheet

LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Columns("D:E").Insert
.Range("D1:E1").Value = Array("Month", "Amount")
For i = LastRow To 2 Step -1

.Rows(i + 1).Resize(11).Insert
For j = 17 To 6 Step -1

.Cells(i + j - 6, "D").Value = .Cells(1, j).Value
.Cells(i + j - 6, "E").Value = .Cells(i, j).Value
Next j
.Cells(i, "A").Resize(, 3).Copy .Cells(i + 1, "A").Resize(11)
Next i

.Columns("F:Q").Delete
End With

With Application

.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With

End Sub

[teeitup]

XLD,

Thanks for your help! The macro works great, except I've more then one year worth of columns is there anyway the macro could include more then 1 year?

Thanks in advance,

Doug

[Bob Phillips]

You'll need to explain that one to me.

[teeitup]

The set of data can be longer then just 2008 it may go out to 2009, 10 or 11. When I run the macro it works perectly for 2008 but doesn't continue out to these additions columns. Is there anyway the macro can count over then way it counts down for the number of rows?

DC