I need to calculate concatenation of the value and how many times it was used in rows as below:
I tried to use this formula by closed-end references
=ARRAYFORMULA(IF(LEN(L3:L9700),M3:M9700&" - "&MMULT((M3:M9700=TRANSPOSE(M3:M9700))*(ROW(M3:M9700)>=TRANSPOSE(ROW(M3:M9700))),SIGN(ROW(M3:M9700)))+0,IFERROR(1/0)))
but it came out error message:
The resulting array was too large
Can advise how to solve this ?
NOTE: This is a follow-up of How to solve Error message in Google Sheet array formula: The resulting array was too large
mmult()
uses matrix multiplication and will only work up to 3162 rows, i.e., 10 million values in the matrix. Usecountifs()
as shown by Erik below to handle a larger number of rows.