Group/Sort records in Excel
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? |
Group/Sort records in Excel
Curious wrote:
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. |
Group/Sort records in Excel
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? |
Group/Sort records in Excel
Curious wrote:
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. |
All times are GMT +1. The time now is 03:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com