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. 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!

[Miguel Zapico]

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
>
>

[Freezerbird]

Thanks very much, that works a treat!