Forum Discussion

diegol81's avatar
diegol81
Copper Contributor
Oct 28, 2021
Solved

Formula to aggregate matrix spilling both rows and columns

Hi all,   I have an array originating in one cell and spilling both rows and colums: C5={5,2,1;7,3,4;9,10,8}   I also have row and column names that describe its contents: B5={"A";"A";"B"} C4=...
excel
Formulas and Functions
office 365
  • SergeiBaklan's avatar
    SergeiBaklan
    Feb 21, 2022

    diegol81 , you are welcome.

     

    For data only it's simpler

    uHeader = UNIQUE(header, 1);
uLabels = UNIQUE(labels);

onlyData=
MAKEARRAY( ROWS(ulabels), COLUMNS(uHeader),
    LAMBDA( r, c,
        SUM(
            FILTER(
                FILTER(
                    data,
                    (labels=INDEX(uLabels, r))  ),
                header=INDEX(uHeader, c)
            )
        )
    )
);
diegol81's avatar
diegol81
Copper Contributor
Feb 21, 2022

SergeiBaklan 

 

Many thanks for that! After the first look at your solution, I feel relieved that I wouldn't have been successful, if ever, before several days of goes at it.

 

Your solution is almost exactly what I was looking for. I'd like the labels to be produced by UNIQUEs on ungrouped data, so the formula should only generate the inner {12,10;9,18}. After such a robust starting point I hope I can adjust it myself.

 

Have a nice day!

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Feb 21, 2022

diegol81 , you are welcome.

 

For data only it's simpler

uHeader = UNIQUE(header, 1);
uLabels = UNIQUE(labels);

onlyData=
MAKEARRAY( ROWS(ulabels), COLUMNS(uHeader),
    LAMBDA( r, c,
        SUM(
            FILTER(
                FILTER(
                    data,
                    (labels=INDEX(uLabels, r))  ),
                header=INDEX(uHeader, c)
            )
        )
    )
);
  • diegol81's avatar
    diegol81
    Copper Contributor
    Feb 21, 2022

    SergeiBaklan 

    That's just perfect.

    Thank you!!!!

     

    Regards,

    Diego

      • diegol81's avatar
        diegol81
        Copper Contributor
        Mar 24, 2022

        SergeiBaklan

         

        I geared the solution a bit more towards the shape I want it to have. I ended up with a single name definition, the lambda itself (actually a lambda called within another lambda), that acts a formula (SpillSum as I named it) with 3 arguments: Data, Labels, Headers.

         

        The intention is that the formula can be used within any workbook, any number of times, without the need of defining each of the components as a name time and again. Only requisite is to define SpillSum once in each workbook.

         

        As you'll see, it very much sticks to the general algorithm you designed. Thanks again!

Resources