Pivot Table help needed pleeeasse!!!!

[HamelinLou]

Hello there!


I work with pivot tables a lot and would really like to know if an Excel Wizard out there could help me with a shortcut or some advise please...


I often recieve large amounts of sales data, and it is set up to show each product's volume sold in a column for each weeks data.


But to be able to pivot it to look at the data by month or year even, I spend a long time creating a column entitled 'month' and 'year' and a single column for 'volume'

I then copy and paste the list of products under the first lot of products and then populate the weekly data into my three new columns.


This means I have a huge spreadsheet!!! and it is a massive copy and paste job!


Please can you let me know if there is a quicker or more effective way to look at this data!


....I hope this makes sense!


Louise x

 

[Luke M]

Hi Louise!

Could you show us a short example of how the data is arranged when given to you, and what you need it to be? To help keep things aligned, I recommend using some back ticks (key usually above the Tab key) to surround your data, so you get something like this:

[pre]

Product   Week
A        1
B        2
C        3
etc.

[/pre]

 

[HamelinLou]

Hi Luke,


Happy Friday!


Yes of course.....


So this is basically what my data looks like when it comes in.


Code Description 01/01/2012 08/01/2012 15/01/2012

BX27464 Red Lever Arch File 67 42 57

QR43537 Black Ring Binder 32 11 75


and I spend my time converting it to this;


Code Description Volume Week Month Year

BX27464 Red Lever Arch File 67 1 Jan 2012

QR43537 Black Ring Binder 32 1 Jan 2012

BX27464 Red Lever Arch File 42 2 Jan 2012

QR43537 Black Ring Binder 11 2 Jan 2012

BX27464 Red Lever Arch File 57 3 Jan 2012

QR43537 Black Ring Binder 75 3 Jan 2012


....So you can see it can be tim e consuming when I'm swamped with 3 years of transactional sales data :-S


Thank you kindly,


Lou

 

[HamelinLou]

D'oh!!!!!


oh god that didnt help at all.....


let me copy and paste, back in a sec...

 

[HamelinLou]

As I receive it...

[pre]

Code	Desc	             1/1/2012	8/1/201    15/01/2012
BX24272	Red Lever Arch File	45	  63	       72
QC76354	Black Ring Binder	11	  18	       26	

How I would like it...					

Code	Desc	             Volume	Week	Month	Year
BX24272	Red Lever Arch File	45	1	Jan	2012
QC76354	Black Ring Binder	11	1	Jan	2012
BX24272	Red Lever Arch File	63	2	Jan	2012
QC76354	Black Ring Binder	18	2	Jan	2012
BX24272	Red Lever Arch File	72	3	Jan	2012
QC76354	Black Ring Binder	26	3	Jan	2012

[/pre]

 

[HamelinLou]

Ok.... so that wasn't much help either :-(

 

[Luke M]

I've edited your post to make it clearer....now to try and solve the problem.

 

[Luke M]

Ok, I think I've got a solution for you. This macro rearranges all the data like you desired, by moving it over to col AA. You should be able to just paste this macro into a module (Alt+F11, Insert, Module) and then select the sheet with your data on it and run the macro.

[pre]

Sub RearrangeData()
Dim LastRow As Integer
Dim LastCol As Integer
Dim OutputRow As Integer
Dim InputRow As Integer
Dim xCode As String
Dim xDesc As String
Dim xDate As Date

Application.ScreenUpdating = False
'Determine the last row to look at
LastRow = Range("A65536").End(xlUp).Row
OutputRow = 2

Cells(1, "AA") = "Code"
Cells(1, "AB") = "Desc"
Cells(1, "AC") = "Volume"
Cells(1, "AD") = "Week"
Cells(1, "AE") = "Month"
Cells(1, "AF") = "Year"

For InputRow = 2 To LastRow
LastCol = Cells(InputRow, "A").End(xlToRight).Column
xCode = Cells(InputRow, "A").Value
xDesc = Cells(InputRow, "B").Value
For i = 3 To LastCol
Cells(OutputRow, "AA") = xCode
Cells(OutputRow, "AB") = xDesc
Cells(OutputRow, "AC") = Cells(InputRow, i).Value
xDate = Cells(1, i).Value
Cells(OutputRow, "AD") = Day(xDate)
Cells(OutputRow, "AE") = Format(xDate, "mmm")
Cells(OutputRow, "AF") = Year(xDate)
OutputRow = OutputRow + 1
Next i
Next InputRow
Application.ScreenUpdating = False
End Sub

[/pre]

 

[Kyle McGhee]

Here is an alternative method [assumes Excel 2007+]:

Using pivot table multiple consolidation ranges will help alter the data as desired.

First, remove the product description so your data looks like this:

[pre]

Code	1/1/2012	1/8/2012	1/15/2012
BX24272	      45	      63	       72
QC76354	      11	      18	       26
[Keep the code and product description in a separate Lookup table.]


Now press Alt, then D then P, which will bring up the pivot table wizard.


Step 1 of 3: Select Multiple Consolidation ranges and PivotTable from the radio button options and press next


Step 2 of 3: Select “I will create the page fields” and press next


Step 3 of 3: Select your data and press Add.  Keep Page fields at 0 and then press finish


This will bring up a PivotTable with fields Row, Column and Value in the Field List.

-Unselect Row and Column, leaving just the Value Field selected.

-Double click the number remaining in the PivotTable (under Sum of Value).  This will give you the data in the desired format, on a new sheet.


Row	Column	     Value
BX24272	1/1/2012	45
BX24272	1/8/2012	63
BX24272	1/15/2012	72
QC76354	1/1/2012	11
QC76354	1/8/2012	18
QC76354	1/15/2012	26

[/pre]
And if you have XL 2007+, it will be in a table so you can quickly fill in the rest of your formulas.

For Week Number enter this formula next to the 45, which will automatically copy down to the end of the table:

=WEEKNUM([[#This Row],[Column]])

For month name:

=TEXT(MONTH([[#This Row],[Column]]),"MMM")

For Year:

=YEAR([[#This Row],[Column]])

And then you can re-insert the product description with a Lookup formula and rename the headers.


If you do it frequently you can record the steps in a macro and use it for later. The macro would only require modification to take into account the initial range for consolidation and the name of the created PivotTable.


Kyle


Source of Tip:

http://blog.contextures.com/archives/2011/07/20/normalize-data-for-excel-pivot-table/