Forum Discussion
Help with MMULT for a range containing 100,000+ values
I have been trying to solve a problem for some time now but unable to find an acceptable solution and hence requesting help from you. The attached has a snapshot of what I am trying to achieve. The ...
The file can be accessed through the following link:
https://1drv.ms/x/s!Aqyz05eCPz3-rTQNZBIvJOYSwdGJ?e=b2onEj
https://1drv.ms/x/s!Aqyz05eCPz3-rTQNZBIvJOYSwdGJ?e=b2onEj
dipankar_de Thanks! Not sure what I'm looking at but why not simply use this in C20:
=SUMIF(B2#,UNIQUE(B2#),C2#)- The actual list has 100,000+ rows and there are 6 columns that need to be individually summed when a duplicate is encountered.
I have tried SUMIFS but that's taking around 14 minutes to process the whole list. That's the reason I am trying to solve this with MMULT and in doing so since the list is long, MMULT is ending up consuming the whole of the memory since it's generating a 100,000+ x 100,000+ matrix.
If somehow, the matrix can be made limited to the number of duplicates for each element (in my example a, b, c, d), possibly the result will be achieved in less than a minute.- Another thing is that I am trying to get the solution through dynamic spill array formulas... one formula and that's it for processing all 100,000+ rows
