Group/Sort records in Excel

[Curious]

Hi,

I have the following in Excel. I'll need to add the "Shares" for each
"Security" by "Side" for each "Class".

Class_AccountName,Security,Side,Shares
------------------------------------
SmallCap_Account1,AA,Buy,1000
SmallCap_Account1,BB,Buy,1000
SmallCap_Account1,CC,Buy,1000
SmallCap_Account1,DD,Buy,1000
SmallCap_Account1,EE,Buy,1000
SmallCap_Account1,AA,Sell,1000
SmallCap_Account1,CC,Sell,1000
SmallCap_Account1,GG,Sell,1000
------------------------------------
SmallCap_Account2,AA,Buy,1000
SmallCap_Account2,CC,Buy,1000
SmallCap_Account2,DD,Buy,1000
SmallCap_Account2,AA,Sell,1000
SmallCap_Account2,BB,Sell,1000
------------------------------------
SmallCap_Account3,AA,Buy,1000
SmallCap_Account3,BB,Buy,1000
SmallCap_Account3,DD,Buy,1000
SmallCap_Account3,EE,Buy,1000
SmallCap_Account3,BB,Sell,1000
SmallCap_Account3,CC,Sell,1000
SmallCap_Account3,EE,Sell,1000
------------------------------------
MidCap_Acct1,AAA,Buy,1000
MidCap_Acct1,BBB,Buy,1000
MidCap_Acct1,CCC,Buy,1000
MidCap_Acct1,EEE,Buy,1000
MidCap_Acct1,AAA,Sell,1000
MidCap_Acct1,DDD,Sell,1000
MidCap_Acct1,EEE,Sell,1000
MidCap_Acct1,HHH,Sell,1000
MidCap_Acct1,XXX,Sell,1000
MidCap_Acct1,YYY,Sell,1000
------------------------------------
MidCap_Acct2,AAA,Buy,1000
MidCap_Acct2,CCC,Buy,1000
MidCap_Acct2,EEE,Buy,1000
MidCap_Acct2,GGG,Buy,1000
MidCap_Acct2,AAA,Sell,1000
MidCap_Acct2,EEE,Sell,1000
MidCap_Acct2,OOO,Sell,1000
MidCap_Acct2,XXX,Sell,1000
MidCap_Acct2,YYY,Sell,1000
------------------------------------

The ideal result for the example looks like the follows:

SmallCap:

AA,Buy,3000 (all of the shares for AA on Buy side are added in
SmallCap)
BB,Buy,2000
CC,Buy,2000
DD,Buy,3000
EE,Buy,2000
AA,Sell,2000 (all of the shares for AA on Sell side are added in
SmallCap)
BB,Sell,2000
CC,Sell,2000
EE,Sell,1000
GG,Sell,1000

MidCap:

AAA,Buy,2000 (all of the shares for AAA on Buy side are added in
MidCap)
BBB,Buy,1000
CCC,Buy,2000
EEE,Buy,2000
GGG,Buy,1000
AAA,Sell,2000 (all of the shares for AAA on Sell side are added in
MidCap)
DDD,Sell,1000
EEE,Sell,2000
HHH,Sell,1000
OOO,Sell,1000
XXX,Sell,2000
YYY,Sell,2000

Anyone can advise me on how to get the ideal results in Excel using
Excel commands?

 

[smartin]

Hi,

I have the following in Excel. I'll need to add the "Shares" for each
"Security" by "Side" for each "Class".

Class_AccountName,Security,Side,Shares

I'm assuming you used commas to denote column breaks.

Since you don't want AccountName in the analysis but you do want Class
it is best to separate these to their own columns.

i.e., instead of this:
Class_AccountName,Security,Side,Shares

do this:
Class,AccountName,Security,Side,Shares

Then a pivot table can summarize any grouping you like. Leave account
name out of the final PT analysis, include class, security, and side in
any of row, column, or page areas, and sum of shares in the data area.

 

[Curious]

Yes, I use comma as column separator.
How can I separate the Class and AccountName automatically in the
Excel?

Also, what command shall I use in order to add up the Shares for the
same Security on the same Side within the same Class?

 

[smartin]

Yes, I use comma as column separator.
How can I separate the Class and AccountName automatically in the
Excel?

Also, what command shall I use in order to add up the Shares for the
same Security on the same Side within the same Class?

You can use text-to-columns to separate the data.

Your original data looks like this:
Class_AccountName,Security,Side,Shares

Use text-to-columns with a comma (,) as delimiter to get this:
Class_AccountName Security Side Shares

Then text-to-columns once more with underscore (_) as delimiter to get this:
Class AccountName Security Side Shares


To get the sums I think your best bet is to use a pivot table as this
will produce all the subtotals at once. If you are not familiar with
pivot tables googling should reveal many good primers.