Is there a dynamic array alternative to this formula?

[valmir]

Hi Everyone!
I have this formula and the problem with it is that since there are so many rows to fill, it takes long to calculate threads and so on, so I wonder if there is a dynamic array alternative for it that does not require so many calculations:

=IFERROR(TEXTJOIN(", "; TRUE; FILTER(Seasons!$O:$O; Seasons!B:B=Stats!B2));"")

For example, I have this formula

=SORT(UNIQUE(FILTER(Seasons!B:B;NOT(ISNUMBER(VALUE(SUBSTITUTE(Seasons!B:B;"-";"")))))))

that works perfectly as an array. Thanks

 

[valmir]

In case there is no alternative, please let me know. I'll mark it as solved too!

 

[Fluff]

You are already using dynamic arrays.
The reason it's slow is that you are using whole column references. If you change the formula to look at a smaller range, it will improve the performance.

 

[valmir]

You are already using dynamic arrays.
The reason it's slow is that you are using whole column references. If you change the formula to look at a smaller range, it will improve the performance.

Thanks for the explanation Fluff. Maybe I didn't make myself clear. Some formulas automatically expand to cover the entire range, I mean, you don't have to use as many formulas as rows to be filled. That's the type of formula that I am talking about

 

[Fluff]

Fraid I do not understand what you are saying.
If you mean a Spill range, then Textjoin will not spill.

 

[valmir]

Yeah! spill range. That's it! Ok got it! Thanks for the explanation!

 

[Fluff]

You're welcome & thanks for the feedback.

 

[valmir]

Hi Fluff! I would like to share this information with you, maybe I can learn something else from you that really is puzzling me: I got this formula from someone:

=IF(COUNTIF(Seasons!$B$1:$B$10000;B2)=0;"";IF(COUNTIF(Seasons!$B$1:$B$10000;B2)=1;INDEX(Seasons!$O$1:$O$10000;MATCH(B2;Seasons!$B$1:$B$10000;0));
INDEX(Seasons!$O$1:$O$10000;MAX(IF(Seasons!$B$1:$B$10000=B2;ROW(Seasons!$B$1:$B$10000))))&" - "&INDEX(Seasons!$O$1:$O$10000;MIN(IF(Seasons!$B$1:$B$10000=B2;ROW(Seasons!$B$1:$B$10000))))))

Not only it solved my problem but most importantly, it is so "light", if I'm making myself understood. By "light" I mean, I don't see any "calculations" going on anymore whenever I work with the file and the file opens instantly just as it did before I was using the initial formulas. So my question is. Why is it that the previous formula was so "heavy" making the file so slow to open whereas this one has virtually no effect on the file in terms of calculations?

 

[Fluff]

I already explained why your formula was slow in post#3

 

[valmir]

I already explained why your formula was slow in post#3

Oh I see, now I got what you meant by that. Sorry, I didn't quite get it first!