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. Can
anyone help me to reorganise it please?

I'm analysing sales data for several products, by monthly sales over
three years. The data is sent to me in the following format: the column
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 different
line (data series) for each year. I think to do this I need to convert
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 "Jan
sales", "Feb sales" etc so that they each appear on a separate row that
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!


--
Freezerbird
------------------------------------------------------------------------
Freezerbird's Profile: http://www.excelforum.com/member.php...o&userid=35286
View this thread: http://www.excelforum.com/showthread...hreadid=550706

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" wrote:


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. Can
anyone help me to reorganise it please?

I'm analysing sales data for several products, by monthly sales over
three years. The data is sent to me in the following format: the column
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 different
line (data series) for each year. I think to do this I need to convert
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 "Jan
sales", "Feb sales" etc so that they each appear on a separate row that
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!


--
Freezerbird
------------------------------------------------------------------------
Freezerbird's Profile: http://www.excelforum.com/member.php...o&userid=35286
View this thread: http://www.excelforum.com/showthread...hreadid=550706

Thanks very much, that works a treat!


--
Freezerbird
------------------------------------------------------------------------
Freezerbird's Profile: http://www.excelforum.com/member.php...o&userid=35286
View this thread: http://www.excelforum.com/showthread...hreadid=550706