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={1,2,2}

 

Where both vectors attempt to represent categorical data.

 

What I'd like to get is a new, more aggregated array, where the row and column vectors are grouped by their distinct (unique) elements, and the original array elements added.

 

I could achieve this, for example, by using an old-school array formula (only difference with legacy Excel being I can now enter it without CSE) that I can copy across the output range. See example file.

 

But, I'd like the process to be dynamic so if the original matrix or any of its vectors change, the result will adjust accordingly. I could achieve dynamic row and column totals using the MMULT / SEQUENCE combo, but this is turning out to be very challenging.

Any ideas would be appreciated!

 

Thanks in advance

Diegol

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)
            )
        )
    )
);

11 Replies

  • 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)
                )
            )
        )
    )
);

Resources