N00b Pivot Table Question

[Reshaw]

Hi there
I am reasonably familiar with Excel but never had to use pivot tables before, so I am asking 2 questions: Is a pivot table the best way to do what I need and if so how to do the summing thing described in a mo
Firstly the Data

I have a flat file with Container number, Product code, description, case qty and location (among other unimportant details)
For each container there could be 30-50 rows representing an individual pallet and varying product codes. (typically there would be between 4 and 12 different products on each container, and then there is the case quantity.

My ultimate goal is to sum all the cases of like products for a given container - I used the wizard thing for pivot tables and it gives me the results something like the following:

Container_1
Product_1
24
12
18
Product_2
10
6
14
Container_2
Product_1
36
48
12
Product_3
2
4

etc...

What I really need to do is add up all the indivdual case quantities instead of listing them as it does above.
So:
Container_1
Product_1
54
Product_2
20

etc.

So....
Question 1 - Is this the best way of going about it?
Question 2 - How can I make a pivot table add up instead of listing the values?


Many thanks in advance
Rob

 

[Andrew Poulsom]

Welcome to MrExcel.

Please post a small but representative example of your raw data.

 

[MartinL]

Hi Rob

Try a layout like this

Excel Workbook
	B	C	D
2	Container	Product	Value
3	Container_1	Product_1	24
4	Container_1	Product_1	12
5	Container_1	Product_1	18
6	Container_1	Product_2	10
7	Container_1	Product_2	6
8	Container_1	Product_2	14
9	Container_2	Product_1	36
10	Container_2	Product_1	48
11	Container_2	Product_1	12
12	Container_2	Product_3	2
13	Container_2	Product_3	4
14
15	Container	Container_1
16
17	Row Labels	Sum of Value
18	Product_1	54
19	Product_2	30
20	Grand Total	84
Sheet2

download my example here http://www.box.net/shared/2r1v0tqblu

 

[Misca]

I suppose your data is sorted so that the Container numbers, Product codes etc. are found in their own columns. If so, I'd use PivotTables without a doubt.

To make PivotTable add up instead of listing the values sounds like you're using too many row ranges: If these two are the only criteria and there were say 10 containers and 50 different products I'd have the containers in column ranges and the products in row ranges + drop a text field in the data field so that I'm getting the count of rows for each field instead of the sum of values (or you can change this manually as well).

There's a couple of really good on line tutorials that I know of that you might be interested in:
http://www.contextures.com/xlPivot01.html and
http://www.datapigtechnologies.com/ExcelMain.htm if you prefer to watch videos.

 

[Reshaw]

<TABLE style="WIDTH: 512pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=681><COLGROUP><COL style="WIDTH: 29pt; mso-width-source: userset; mso-width-alt: 1389" width=38><COL style="WIDTH: 78pt; mso-width-source: userset; mso-width-alt: 3803" width=104><COL style="WIDTH: 194pt; mso-width-source: userset; mso-width-alt: 9435" width=258><COL style="WIDTH: 33pt; mso-width-source: userset; mso-width-alt: 1609" width=44><COL style="WIDTH: 49pt; mso-width-source: userset; mso-width-alt: 2377" width=65><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3693" width=101><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 29pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20 width=38>PLT #</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 78pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl65 width=104>PRODUCT CODE</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 194pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" width=258>DESCRIPTION</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 33pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=44>UNITS</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 49pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=65>CASE QTY</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 53pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=71>LOCATION</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 76pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl64 width=101>CONTAINER</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>1</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>27847732</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>PP CORDLESS HAMMER DRILL 24V</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>RH1501</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>TTNU9418047</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>2</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>27847732</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>PP CORDLESS HAMMER DRILL 24V</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>12</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>RH1401</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>TTNU9418047</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>3</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>27847848</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>PP 300W 1/2 SHEET SANDER</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>RH1301</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>TTNU9418047</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 height=20>4</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68>27847848</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl69>PP 300W 1/2 SHEET SANDER</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>5</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>18</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>RH1302</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: #fcd5b4; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl70>TTNU9418047</TD></TR></TBODY></TABLE>


Sample data

 

[Reshaw]

Re above (if noone posts by the time I finish this)

I need to produce a report that says

TTNU9418047

27847732

24
​

27847848

36
​

NExt Container etc. etc.

Just realised that there is only on container on the smaple data but you get the idea there are many differnet container numbers containing many pallets of different stock..

Thanks again

 

[Andrew Poulsom]

Create a pivot table with CONTAINER and PRODUCT CODE in the Row field and Sum of CASE QTY in the Data field. Example:

<TABLE style="WIDTH: 213pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=284 border=0 x:str><COLGROUP><COL style="WIDTH: 92pt; mso-width-source: userset; mso-width-alt: 4461" width=122><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" width=127><COL style="WIDTH: 26pt; mso-width-source: userset; mso-width-alt: 1280" width=35><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 92pt; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" width=122 height=18>Sum of CASE QTY</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; WIDTH: 95pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=127> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; WIDTH: 26pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=35> </TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>CONTAINER</TD><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">PRODUCT CODE</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Total</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>TTNU9418047</TD><TD class=xl22 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>27847732</TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>24</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl25 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18> </TD><TD class=xl26 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>27847848</TD><TD class=xl27 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>36</TD></TR>

<TR style="HEIGHT: 13.5pt" height=18><TD class=xl22 id=td_post_2630932 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18>TTNU9418047 Total</TD><TD class=xl23 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"> </TD><TD class=xl24 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" align=right x:num>60</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl28 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>Grand Total</TD><TD class=xl29 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl30 style="BORDER-RIGHT: black 0.5pt solid; BORDER-TOP: black 0.5pt solid; BORDER-LEFT: black 0.5pt solid; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>60</TD></TR></TBODY></TABLE>

If you want the total number of units, add a column to your data with a formula that multiples units by case qty, and add that to the pivot table's Data field.

 

[BGY23]

A pivot table is the way to go. I just extrapolted your data to include multiple Container numbers and so have 3 containers, 12 pallets.

I then used a pivot table over the data and got total units by Container and Part number.

 

[Reshaw]

Apologies for my inexperience but I am not sure whaere the Data field is/are:

Screen grab should be here:

I can see the Row fields section but nothing called Data fields

Thanks

 

[Andrew Poulsom]

I notice that the entries in the UNITS and CASE QTY columns are left justified. This indicates that they may be text rather than numbers. You can quickly convert them using Data|Text To Columns and clicking Finish.