re: What's the code ?

[ashwin28]

re: What's the code ?

Hi,

I have the following sales data which I want to analyse sales-person wise. Our company traditionally sells oranges & apples and has now diversified into selling other fruits. I would like to analyse the sales person wise sales into traditional sales (Oranges &Apples) in one column and rest of brands put together in another column.

I tried but I'm getting stumped in the code ? Pls help ?

Thanks,

Ash



SALES PERSON BRAND SALES
ALAN ORANGES 12
ALAN APPLES 22
ALAN BANANAS 37
ALAN LEMONS 17
ALAN PEACHES 7
ALAN PINEAPPLES 32
MIKE ORANGES 7
MIKE APPLES 9
MIKE BANANAS 27
MIKE LEMONS 32
MIKE PEACHES 34
MIKE PINEAPPLES 42
SMITH BANANAS 12
SMITH LEMONS 12
SMITH PEACHES 22
SMITH PINEAPPLES 22

 

[Ceduljko]

Don't use any code - just make a PivotTable (with PivotChart, if you'd like) and Excel is going to do it for you.

 

[ashwin28]

Hi,

I used Pivot Tables but I dont know how to sum 2 columns say Oranges and Apples together and display that in the Pivot report.

Currently, the Pivot gives me sales person wise brand wise columns.

ash

 

[GlennUK]

Experiment with the Grouping button.

 

[Ceduljko]

You're on a good way towards the solution

Another tip: Calculated fields - try to make a calculated field with some IF statements in it...

If Apples or Oranges -> Value returned: Traditional
If not -> Not Traditional.

In that way you can have additional grouping criteria.

 

[ashwin28]

Hi,

Does the grouping function work on a pivot table report ? If i try it, it says cannot do it. Also, why cannot I do a simple SUM function on the resulting pivot report by selecting the columns I want to add ?

thanks

ashwin

 

[Ceduljko]

You can also add an additional column to your data, like this:

Book1
	A	B	C	D
1	SalesPerson	Brand	Sales	Traditionalornot
2	Alan	Oranges	12	Traditional
3	Alan	Apples	22	Traditional
4	Alan	Bananas	37	Bananas
5	Alan	Lemons	17	Lemons
6	Alan	Peaches	7	Peaches
7	Alan	Pineapples	32	Pineapples
8	Mike	Oranges	7	Traditional
9	Mike	Apples	9	Traditional
10	Mike	Bananas	27	Bananas
11	Mike	Lemons	32	Lemons
12	Mike	Peaches	34	Peaches
13	Mike	Pineapples	42	Pineapples
14	Smith	Bananas	12	Bananas
15	Smith	Lemons	12	Lemons
16	Smith	Peaches	22	Peaches
17	Smith	Pineapples	22	Pineapples
Sheet1

 

[GlennUK]

Grouping

Here is a screen-shot after the first phase of grouping ... after Oranges and Apples were dragged together, then selected and the Group button pressed, and then the rest of the items selected, and Group pressed again:

Book1
	A	B	C	D	E	F	G	H	I	J	K
1	SALES PERSON	BRAND	SALES	Sum of SALES	BRAND2	BRAND
2	ALAN	ORANGES	12		Group1		Group2				Grand Total
3	ALAN	APPLES	22	SALES PERSON	APPLES	ORANGES	BANANAS	LEMONS	PEACHES	PINEAPPLES
4	ALAN	BANANAS	37	ALAN	22	12	37	17	7	32	127
5	ALAN	LEMONS	17	MIKE	9	7	27	32	34	42	151
6	ALAN	PEACHES	7	SMITH			12	12	22	22	68
7	ALAN	PINEAPPLES	32	Grand Total	31	19	76	61	63	96	346
8	MIKE	ORANGES	7
9	MIKE	APPLES	9
10	MIKE	BANANAS	27
11	MIKE	LEMONS	32
12	MIKE	PEACHES	34
13	MIKE	PINEAPPLES	42
14	SMITH	BANANAS	12
15	SMITH	LEMONS	12
16	SMITH	PEACHES	22
17	SMITH	PINEAPPLES	22
Sheet3

The next stage is to remove the original brand field, just leaving 2 columns of data.

 

[GlennUK]

After ..

After remoing original brand field ( just drag it off the table ), it looks like this:

Book1
	A	B	C	D	E	F	G
1	SALES PERSON	BRAND	SALES	Sum of SALES	BRAND2
2	ALAN	ORANGES	12	SALES PERSON	Trad	Others	Grand Total
3	ALAN	APPLES	22	ALAN	34	93	127
4	ALAN	BANANAS	37	MIKE	16	135	151
5	ALAN	LEMONS	17	SMITH		68	68
6	ALAN	PEACHES	7	Grand Total	50	296	346
7	ALAN	PINEAPPLES	32
8	MIKE	ORANGES	7
9	MIKE	APPLES	9
10	MIKE	BANANAS	27
11	MIKE	LEMONS	32
12	MIKE	PEACHES	34
13	MIKE	PINEAPPLES	42
14	SMITH	BANANAS	12
15	SMITH	LEMONS	12
16	SMITH	PEACHES	22
17	SMITH	PINEAPPLES	22
Sheet3

Have overtyped the Group1 and Group2 strings with something meaningful.