Help with MMULT for a range containing 100,000+ values

Copper Contributor

 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 problem I am facing is when the list goes beyond 100,000 rows, Excel runs out of resource.

 

The list contains duplicates as well as non-duplicates. I believe, if we restrict MMULT only for the duplicates, the "Out of Resource" error won't show up. I want the formula to do the following:

1. If unique (non-duplicate), simply consider the given value

2. If duplicate, then do MMULT (sum) for only the duplicate at hand and move to the next in sequence.

 

In the attached, "a", "b" and "d" are duplicated while "c" is unique. Want the formula to consider only "a" for MMULT and then separately for "b" and so on. Doing it together for "a", "b", "c" and "d" gives the "Out of Resource" error. The formula identifies "c" as unique and copies the corresponding value.

 

Please let me know if further clarification is required.

 

I wanted to upload the file but unfortunately couldn't find a link to do so.

22 Replies
Hello Sergei, the actual count I have is 182,052 of which 94,318 are unique which is ~52% of the total records/rows. This is just representative data and is expected to increase over time. the processing is happening on a Dell Latitude 3400 (Intel i5 processor with 16GB RAM) laptop and my experience with SUMIFS on this laptop on the aforesaid data is rather slow compared to dynamic array formulas.

I am trying to avoid PowerQuery since it will require refresh every time data gets changed. This tool will be run periodically by junior / inexperienced resources who might miss out to refresh after data change and in turn generate an incorrect report / dashboard.

The tool being currently developed is for analyzing and reporting weekly Time-Sheet data (resource utilization) to the leadership and is expected to get extended to include invoicing to clients for T&M contracts which will comprise reconciliation of Time-Sheet data with client captured utilization information.

The whole process will contain a series of Excel workbooks, each performing a specific task, in a predefined sequence.
Hello Peter, I am not really keen to implement copy-fill-down formulas. Reason being sheer size of everchanging data which might run way past the copy-filled formula range.

Since this tool will be run by junior / inexperienced resources, even if I incorporate certain checks to validate correctness / completeness of data, they can very well overlook and ignore such checks and send out an incorrect utilization report. Hence, summarily trying to avoid copy-fill-down formulas.
Hello Peter, if, instead of one "number" column, we have, say 5 "number" columns, how would the formula change to accommodate summing up the 5 column values independently for each unique "letter"?