re: What's the code ?

ashwin28

New Member
Joined
Dec 18, 2005
Messages
11
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
:rolleyes:


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
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Ceduljko

Board Regular
Joined
Dec 28, 2004
Messages
58
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

New Member
Joined
Dec 18, 2005
Messages
11
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
 

Ceduljko

Board Regular
Joined
Dec 28, 2004
Messages
58

ADVERTISEMENT

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

New Member
Joined
Dec 18, 2005
Messages
11
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

Board Regular
Joined
Dec 28, 2004
Messages
58

ADVERTISEMENT

You can also add an additional column to your data, like this:
Book1
ABCD
1SalesPersonBrandSalesTraditionalornot
2AlanOranges12Traditional
3AlanApples22Traditional
4AlanBananas37Bananas
5AlanLemons17Lemons
6AlanPeaches7Peaches
7AlanPineapples32Pineapples
8MikeOranges7Traditional
9MikeApples9Traditional
10MikeBananas27Bananas
11MikeLemons32Lemons
12MikePeaches34Peaches
13MikePineapples42Pineapples
14SmithBananas12Bananas
15SmithLemons12Lemons
16SmithPeaches22Peaches
17SmithPineapples22Pineapples
Sheet1
 

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
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
ABCDEFGHIJK
1SALES PERSONBRANDSALESSum of SALESBRAND2BRAND
2ALANORANGES12Group1Group2Grand Total
3ALANAPPLES22SALES PERSONAPPLESORANGESBANANASLEMONSPEACHESPINEAPPLES
4ALANBANANAS37ALAN22123717732127
5ALANLEMONS17MIKE9727323442151
6ALANPEACHES7SMITH1212222268
7ALANPINEAPPLES32Grand Total311976616396346
8MIKEORANGES7
9MIKEAPPLES9
10MIKEBANANAS27
11MIKELEMONS32
12MIKEPEACHES34
13MIKEPINEAPPLES42
14SMITHBANANAS12
15SMITHLEMONS12
16SMITHPEACHES22
17SMITHPINEAPPLES22
Sheet3


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

GlennUK

Well-known Member
Joined
Jul 8, 2002
Messages
11,402
After ..

After remoing original brand field ( just drag it off the table ), it looks like this:
Book1
ABCDEFG
1SALES PERSONBRANDSALESSum of SALESBRAND2
2ALANORANGES12SALES PERSONTradOthersGrand Total
3ALANAPPLES22ALAN3493127
4ALANBANANAS37MIKE16135151
5ALANLEMONS17SMITH6868
6ALANPEACHES7Grand Total50296346
7ALANPINEAPPLES32
8MIKEORANGES7
9MIKEAPPLES9
10MIKEBANANAS27
11MIKELEMONS32
12MIKEPEACHES34
13MIKEPINEAPPLES42
14SMITHBANANAS12
15SMITHLEMONS12
16SMITHPEACHES22
17SMITHPINEAPPLES22
Sheet3


Have overtyped the Group1 and Group2 strings with something meaningful.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,168
Messages
5,570,649
Members
412,335
Latest member
cinciri99
Top