Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
In excel files get saved a [Group], can't sort. Help! | Excel Discussion (Misc queries) | |||
what is a group excel file and how do i disable so i can sort it? | Excel Worksheet Functions | |||
Excel subtotal group sort | Excel Worksheet Functions | |||
Excel should let me group rows of info and then sort them | Excel Worksheet Functions | |||
How do I sort a group of calculated cells in Excel? | Excel Worksheet Functions |