Forum Discussion
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=...
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) ) ) ) );
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!
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!
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)
)
)
)
)
);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!
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) ) ) ) );