Discussion Re: Formula to aggregate matrix spilling both rows and columns in Excel
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3266274#M139702
<P><LI-USER uid="1198883"></LI-USER> </P>
<P>IMHO, that's no straightforward solution. As variant you may use AFE <A href="https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambdas-to-production-and-advanced-formula/ba-p/3073293" target="_blank">Announcing LAMBDAs to Production and Advanced Formula Environment, A Microsoft Garage Project</A>, collect your functions in hidden sheet of some file and use it as template. </P>Thu, 24 Mar 2022 21:08:32 GMTSergei Baklan2022-03-24T21:08:32ZFormula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/2896401#M119922
<P>Hi all,</P><P> </P><P>I have an array originating in one cell and spilling both rows and colums:</P><P>C5={5,2,1;7,3,4;9,10,8}</P><P> </P><P>I also have row and column names that describe its contents:</P><P>B5={"A";"A";"B"}</P><P>C4={1,2,2}</P><P> </P><P>Where both vectors attempt to represent categorical data.</P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>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.</P><P>Any ideas would be appreciated!</P><P> </P><P>Thanks in advance</P><P>Diegol</P>Thu, 28 Oct 2021 15:43:32 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/2896401#M119922diegol812021-10-28T15:43:32ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/2896980#M119947
<P>BTW for those interested in getting dynamic row and column totals, see the links below:</P><P><A href="https://exceljet.net/formula/get-row-totals" target="_blank">https://exceljet.net/formula/get-row-totals</A></P><P><A href="https://exceljet.net/formula/get-column-totals" target="_blank">https://exceljet.net/formula/get-column-totals</A></P><P> </P>Thu, 28 Oct 2021 17:56:36 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/2896980#M119947diegol812021-10-28T17:56:36ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3186362#M135165
Finally, I got LAMBDA and its helper functions!<BR />Howerver after a few days of practicing, I could not pull out this twofold, group by-like aggregation in a dynamic manner.<BR />I'll keep on trying as the LAMBDA theory continues settling in my head. If anyone gets lucky, please share!<BR /><BR />Thanks!Fri, 18 Feb 2022 21:57:27 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3186362#M135165diegol812022-02-18T21:57:27ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3187139#M135190
<P><LI-USER uid="1198883"></LI-USER> </P>
<P>For such result</P>
<P><span class="lia-inline-image-display-wrapper lia-image-align-inline" image-alt="image.png" style="width: 196px;"><img src="https://techcommunity.microsoft.com/t5/image/serverpage/image-id/349672i47CD1AF9DF155B85/image-size/large?v=v2&px=999" role="button" title="image.png" alt="image.png" /></span></P>
<P>it could be</P>
<LI-CODE lang="excel-formula">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)
)
)
)
)
);</LI-CODE>Sat, 19 Feb 2022 08:54:45 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3187139#M135190Sergei Baklan2022-02-19T08:54:45ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3193019#M135441
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>Have a nice day!</P>Mon, 21 Feb 2022 12:21:59 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3193019#M135441diegol812022-02-21T12:21:59ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3194023#M135470
<P><LI-USER uid="1198883"></LI-USER> , you are welcome.</P>
<P> </P>
<P>For data only it's simpler</P>
<LI-CODE lang="excel-formula">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)
)
)
)
);
</LI-CODE>Mon, 21 Feb 2022 16:00:41 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3194023#M135470Sergei Baklan2022-02-21T16:00:41ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3194943#M135491
<P><LI-USER uid="521"></LI-USER> </P><P>That's just perfect.</P><P>Thank you!!!!</P><P> </P><P>Regards,</P><P>Diego</P>Mon, 21 Feb 2022 18:34:36 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3194943#M135491diegol812022-02-21T18:34:36ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3195421#M135504
<P><LI-USER uid="1198883"></LI-USER> , glad to help</P>Mon, 21 Feb 2022 20:41:33 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3195421#M135504Sergei Baklan2022-02-21T20:41:33ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3265909#M139657
<P><LI-USER uid="521"></LI-USER></P><P> </P><P>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.</P><P> </P><P>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.</P><P> </P><P>As you'll see, it very much sticks to the general algorithm you designed. Thanks again!</P>Thu, 24 Mar 2022 15:49:39 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3265909#M139657diegol812022-03-24T15:49:39ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3266274#M139702
<P><LI-USER uid="1198883"></LI-USER> </P>
<P>IMHO, that's no straightforward solution. As variant you may use AFE <A href="https://techcommunity.microsoft.com/t5/excel-blog/announcing-lambdas-to-production-and-advanced-formula/ba-p/3073293" target="_blank">Announcing LAMBDAs to Production and Advanced Formula Environment, A Microsoft Garage Project</A>, collect your functions in hidden sheet of some file and use it as template. </P>Thu, 24 Mar 2022 21:08:32 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3266274#M139702Sergei Baklan2022-03-24T21:08:32ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3267309#M139823
<P><LI-USER uid="521"></LI-USER> </P><P> </P><P>Thanks for that. Unfortunately the new AFE addin (actually, all the addins accessible via the Insert | Addins | Get addins dialog) download and install seems to have been disabled globally in my company. Hopefully I'll get the admins to enable it.</P><P> </P><P>Back to my last post: shortly after positing I realised my solution did not fully resemble the functional structure I was seeking, which is akin to that of a SUMIFS: a data array to add-up; a labels vector to evaluate; a labels condition vector; a (transposed) headers vector to evaluate; a (transposed) headers condition vector. I was missing the two condition arguments, which impaired the formula's functionality.</P><P> </P><P>As a result of this review, the LET() call was no longer needed and I renamed the formula SpillSumIfs. Here it goes, hopefully for good.</P>Fri, 25 Mar 2022 22:52:01 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3267309#M139823diegol812022-03-25T22:52:01ZRe: Formula to aggregate matrix spilling both rows and columns
https://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3267531#M139851
<P><LI-USER uid="1198883"></LI-USER> </P>
<P>That's good, thanks for sharing</P>Sat, 26 Mar 2022 10:24:50 GMThttps://techcommunity.microsoft.com/t5/excel/formula-to-aggregate-matrix-spilling-both-rows-and-columns/m-p/3267531#M139851Sergei Baklan2022-03-26T10:24:50Z