How to make a 2-layer Subtotal from a table

Hi, I am trying to make a function for getting a subtotal from a Table. I attached an excel file. First sheet are inputs, second sheet is the desired out. My data consists on three columns: the first layer (financial assets codes), the second layer (fund names where the asset is located) and third column (the amount invested). Any help please... would be deeply appreciated. Regards, Edson.

Answer 1

[Peter Perkins]

Edson, I think this does the trick:

>> question = readtable('question.xlsx')
question = 
     First      Second     Amount
    ________    _______    ______
    'XS0001'    'Fund1'    20    
    'XS0002'    'Fund1'    30    
    'XS0003'    'Fund2'    40    
    'XS0004'    'Fund1'    50    
    'XS0005'    'Fund2'    60    
    'XS0006'    'Fund1'    70    
    'XS0001'    'Fund2'    80    
    'XS0002'    'Fund2'    90    
    'XS0003'    'Fund2'    20    
    'XS0004'    'Fund1'    30    
    'XS0005'    'Fund2'    40    
    'XS0006'    'Fund1'    50    
    'XS0001'    'Fund2'    60    
    'XS0002'    'Fund1'    70    
    'XS0003'    'Fund1'    80    
    'XS0004'    'Fund2'    90    
    'XS0005'    'Fund1'    20    
    'XS0006'    'Fund2'    30    
    'XS0001'    'Fund2'    40    
    'XS0002'    'Fund1'    50    
    'XS0003'    'Fund2'    60    
    'XS0004'    'Fund1'    70    
    'XS0005'    'Fund2'    80    
    'XS0006'    'Fund1'    90    
>> varfun(@sum,question,'GroupingVariable',{'First' 'Second'},'InputVariable','Amount')
ans = 
                     First      Second     GroupCount    sum_Amount
                    ________    _______    __________    __________
    XS0001_Fund1    'XS0001'    'Fund1'    1              20       
    XS0001_Fund2    'XS0001'    'Fund2'    3             180       
    XS0002_Fund1    'XS0002'    'Fund1'    3             150       
    XS0002_Fund2    'XS0002'    'Fund2'    1              90       
    XS0003_Fund1    'XS0003'    'Fund1'    1              80       
    XS0003_Fund2    'XS0003'    'Fund2'    3             120       
    XS0004_Fund1    'XS0004'    'Fund1'    3             150       
    XS0004_Fund2    'XS0004'    'Fund2'    1              90       
    XS0005_Fund1    'XS0005'    'Fund1'    1              20       
    XS0005_Fund2    'XS0005'    'Fund2'    3             180       
    XS0006_Fund1    'XS0006'    'Fund1'    3             210       
    XS0006_Fund2    'XS0006'    'Fund2'    1              30

You might also consider converting First and Second to categorical, something like

question.First = categorical(question.First);

Hope this helps.

[Edson]

Thanks a lot Peter! Works like a charm! That solves my question entirely.

Answer 2

[Edson]

Thanks a lot Peter! Works like a charm! That solves my question entirely.