Pivot Tables - Utterly Confused

[ultratch47]

I know what i want to do, and i am pretty sure that a pivot table is the best approach, however i cant EVER seem to get the pivtot table to come out like i want it. I have found and read tutorials and help files on them, but they never go to in depth.

if anyone has a suggestion, here is my file:
thank in advance

******** LANGUAGE="JavaScript" ************************************************************************>

Microsoft Excel - Book1 ___Running: xl97 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
I2I5 =
	A	B	C	D	E	F	G	H	I
1	Item	Description	Qty	$	*	Item	Description	Qty	$
2	0300	COLLAR	1	*$***19.00*	*	0300	COLLAR	10	*$*190.00*
3	04A5	SHIRT*	3	*$***55.00*	*	04A5	SHIRT*	3	*$***55.00*
4	04BE	SKIRT	5	*$***59.00*	*	04BE	SKIRT	5	*$***59.00*
5	04B7	TEE	1	*$***39.00*	--------->>	04B7	TEE	3	*$*117.00*
6	04CL	CAMISOLE	1	*$***35.00*	*	04CL	CAMISOLE	1	*$***35.00*
7	0300	COLLAR	9	*$***19.00*	*	*	*	*	*
8	04B7	TEE	2	*$***39.00*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[marstonk]

Well to put it in a pivot is easy but I don't how to get a field which will multiply one by the other IE $ by Quantity. Select the data and go to pivot table wizard. Go through the steps until you get the layout option this is the best way to design your pivot I find. The row headings will be the Description and the Price, the data will be the quantity and a product field.

If you cannot find a solution to the total field then just do a multiplication column in your raw data.

Hope this helps.
kitty g

******** ******************** ************************************************************************>

Microsoft Excel - Book1 ___Running: xl2002 XP : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
A3 =
	A	B	C	D	E
3	*	*	*	Data	*
4	Item	Description	$	Sum*of*Qty	Product*of*$
5	300	COLLAR	*$***19.00*	10	0.00
6	04A5	SHIRT*	*$***55.00*	3	0.00
7	04B7	TEE	*$***39.00*	3	0.00
8	04BE	SKIRT	*$***59.00*	5	0.00
9	04CL	CAMISOLE	*$***35.00*	1	0.00
10	Grand*Total	*	*	22	*
Sheet4 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[Ekim]

Your raw data appears to be inconsistent.

You have 10 collars at $19.00 each to give a total of $190.00. However, you have 3 skirts at $55 each (I assume) – shouldn’t the total be $165.00? It seems that your raw data should be this:

******** ******************** ************************************************************************>

Microsoft Excel - Book1-PT.xls ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
E2E3E4E5E6E7E8 =
	A	B	C	D	E
1	Item	Description	Qty	$	Total
2	300	COLLAR	1	19	19
3	04A5	SHIRT*	3	55	165
4	04BE	SKIRT	5	59	295
5	04B7	TEE	1	39	39
6	04CL	CAMISOLE	1	35	35
7	300	COLLAR	9	19	171
8	04B7	TEE	2	39	78
9	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

If so (Excel XP menu system):
Click cell A1.
Go to the Data menu | Pivot Tables.
Next Next.
Where do you want to put the Pivot Table report? – select Existing Worksheet – say G1.
Click the Layout button.
Drag “Item” to row, “Description” to row.
Drag “Qty” to Data, and “Total” to Data (ensure both these items are now showing
“Sum of Qty” and “Sum of Total” respectively.
OK
Finish

The preliminary Pivot Table (PT) will look like this:

******** ******************** ************************************************************************>

Microsoft Excel - Book1-PT.xls ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F1 =
	F	G	H	I	J	K
1	*	Item	Description	Data	Total	*
2	*	300	COLLAR	Sum*of*Qty	10	*
3	*	*	*	Sum*of*Total	190	*
4	*	300*Sum*of*Qty	*	*	10	*
5	*	300*Sum*of*Total	*	*	190	*
6	*	04A5	SHIRT*	Sum*of*Qty	3	*
7	*	*	*	Sum*of*Total	165	*
8	*	04A5*Sum*of*Qty	*	*	3	*
9	*	04A5*Sum*of*Total	*	*	165	*
10	*	04B7	TEE	Sum*of*Qty	3	*
11	*	*	*	Sum*of*Total	117	*
12	*	04B7*Sum*of*Qty	*	*	3	*
13	*	04B7*Sum*of*Total	*	*	117	*
14	*	04BE	SKIRT	Sum*of*Qty	5	*
15	*	*	*	Sum*of*Total	295	*
16	*	04BE*Sum*of*Qty	*	*	5	*
17	*	04BE*Sum*of*Total	*	*	295	*
18	*	04CL	CAMISOLE	Sum*of*Qty	1	*
19	*	*	*	Sum*of*Total	35	*
20	*	04CL*Sum*of*Qty	*	*	1	*
21	*	04CL*Sum*of*Total	*	*	35	*
22	*	Total*Sum*of*Qty	*	*	22	*
23	*	Total*Sum*of*Total	*	*	802	*
24	*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

Click the Data button in cell I1 (colored green for demo purposes) and with your mouse drag the button onto the Total cell (J1) – release the mouse.

Select any cell inside the PT, say cell G3, right click and select “Field Settings” In the Subtotals field, click the radio button for “None”. Then OK.

The final PT Table should now be shown.

HTH

Mike

[Ekim]

The final Pivot Table should look the following:

******** ******************** ************************************************************************>

Microsoft Excel - Book1-PT.xls ___Running: xl2002 XP : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
F1 =
	F	G	H	I	J	K
1	*	*	*	Data	*	*
2	*	Item	Description	Sum*of*Qty	Sum*of*Total	*
3	*	300	COLLAR	10	190	*
4	*	04A5	SHIRT*	3	165	*
5	*	04B7	TEE	3	117	*
6	*	04BE	SKIRT	5	295	*
7	*	04CL	CAMISOLE	1	35	*
8	*	Grand*Total	*	22	802	*
9	*	*	*	*	*	*
Sheet1 *

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

If you don’t want a Grand Total line:
Right click cell G8
Select “Table Options”
Uncheck the check box “Grand totals for columns”
OK

[ultratch47]

ahhhhhhhhhhh

i was getting the same results you were on the beginning table, from there i didnt know how to get a single line.

Thanks very much for your posts