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
Oct 28, 2021

BTW for those interested in getting dynamic row and column totals, see the links below:

https://exceljet.net/formula/get-row-totals

https://exceljet.net/formula/get-column-totals

 

  • diegol81's avatar
    diegol81
    Copper Contributor
    Feb 18, 2022
    Finally, I got LAMBDA and its helper functions!
    Howerver after a few days of practicing, I could not pull out this twofold, group by-like aggregation in a dynamic manner.
    I'll keep on trying as the LAMBDA theory continues settling in my head. If anyone gets lucky, please share!

    Thanks!
    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      Feb 19, 2022

      diegol81 

      For such result

      it could be

      data = Q!$C$5#;
header = Q!$C$4#;
labels = Q!$B$5#;

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

rws = ROWS(ulabels) + 1;
clmns = COLUMNS(uHeader) + 1;

sumData = LAMBDA(lbl, hdr,
    SUM(
        FILTER(
            FILTER(data, (labels = lbl)),
            header = hdr
        )
    )
);

result=
MAKEARRAY(
    rws,
    clmns,
    LAMBDA(r, c,
        IF(
            r = 1,
            IF(c = 1, "", INDEX(uHeader, c - 1)),
            IF(
                c = 1,
                INDEX(uLabels, r - 1),
                sumData(
                    INDEX(uLabels, r - 1),
                    INDEX(uHeader, c - 1)
                )
            )
        )
    )
);
      • 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!

Resources