0

I need to calculate concatenation of the value and how many times it was used in rows as below:
1

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

Improve this question
1
  • 1
    mmult() uses matrix multiplication and will only work up to 3162 rows, i.e., 10 million values in the matrix. Use countifs() as shown by Erik below to handle a larger number of rows.
    – doubleunary
    Aug 14, 2021 at 8:57

2 Answers 2

Reset to default
1

Delete M3:M and place the following in M3:

=ArrayFormula(IF(L3:L="",,PROPER(L3:L)&" - "&COUNTIFS(LOWER(L3:L),LOWER(L3:L),ROW(L3:L),"<="&ROW(L3:L))))

PROPER and LOWER are just used to assure that "boat" would match "Boat" and that the output would always be capitalized. The rest basically just asks for the count of matches up to and including each row.

Improve this answer
1

If you really need ~9700 rows on each reference then it's very likely that you will need to use a different approach as 9700^2 = 94 090 000 (this is the number of cells required by MMULT in your formula) exceeds the array size limit.

One option is to split the data across multiple spreadsheets. You will have to adjust your formulas on each spreadsheet.

Related

Improve this answer
2
  • I do not think the five million cells per spreadsheet limit is relevant here. mmult() will handle ten million values in its matrix. That matrix is not saved anywhere so spreadsheet cell limits do not come into the play.
    – doubleunary
    Aug 14, 2021 at 9:01
  • @doubleunary You are right. Thanks for the feedback.
    – Rubén ♦
    Aug 14, 2021 at 15:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.