Converting data to list format

[Freezerbird]

I believe that to analyse my data using pivot tables and pivot charts
my raw data must be formatted as a "list". Unfortunately it isn't. Ca
anyone help me to reorganise it please?

I'm analysing sales data for several products, by monthly sales ove
three years. The data is sent to me in the following format: the colum
headers are product, year, sales in Jan, sales in Feb, sales in Mar....
sales in Dec. I want a pivot chart with the product in the page field
sales figures up the side and months along the bottom, with a differen
line (data series) for each year. I think to do this I need to conver
my data so that the columns headers are product, year, month and sales
and apply the pivot table to that.

How can I quickly transfer the sales figures in the columns "Ja
sales", "Feb sales" etc so that they each appear on a separate row tha
states the month in one column and the sales for that month in another
Do I actually need to do it like this?

Any help gratefully received

 

[Guest]

You may use a macro like this:

Sub MakeList()
Dim rngTable, rngList As Range
Dim i, j, k

'Change the ranges to reflect the correct data origin and destiny
Set rngTable = Worksheets("Sheet1").Range("A1").CurrentRegion
Set rngList = Worksheets("Sheet2").Range("A1")
k = 1

'Loop over the origin table, writing in the destination list
With rngTable
'First number is a two because of the headers
For i = 2 To .Rows.Count
'First number is a 3, the column where the data begins. Change
if appropiate
For j = 3 To .Columns.Count
rngList.Offset(k, 0) = .Cells(i, 1).Value
rngList.Offset(k, 1) = .Cells(i, 2).Value
rngList.Offset(k, 2) = .Cells(1, j).Value
rngList.Offset(k, 3) = .Cells(i, j).Value
k = k + 1
Next
Next
End With
End Sub

Change the ranges and the FOR clauses as appropiate for your data.

Hope this helps,
Miguel.

 

[Freezerbird]

Thanks very much, that works a treat