Forum Discussion
Limiting the length of results from Dynamic / Spill Arrays
I am trying to figure out a way to limit the number of results returned from a dynamic array such as =UNIQUE or =FILTER. So for example, say I want to only want to return the first 10 unique values from a list in column A. Using =UNIQUE(A:A) alone will return all unique values.
The best solution I have is to do something like wrap the UNIQUE formula in an INDEX formula: =INDEX(UNIQUE(A:A),ROW(),1), and copy that down 10 rows, but I imagine this is very performance heavy as it is effectively calculating 10 "UNIQUE" formulas, rather than 1 that spills.
Is there any native way to return a specific number of results from a Dynamic Array / Spill formula?
= INDEX( UNIQUE(ColumnA), SEQUENCE(10) )
would return 10 values and
= INDEX( UNIQUE(ColumnA), SEQUENCE(1,10) )
would transpose the result array.
6 Replies
I don't use direct cell referencing so this will probably look strange.
= LET( distinct0, UNIQUE(ColumnA), distinct1, FILTER(distinct0, distinct0<>""), N, COUNTA(distinct1), k, SEQUENCE(MIN(N, 10)), INDEX( distinct1, k ) )What was the result of the other query that Sergei noted?
= INDEX( UNIQUE(ColumnA), SEQUENCE(10) )
would return 10 values and
= INDEX( UNIQUE(ColumnA), SEQUENCE(1,10) )
would transpose the result array.
Thanks! I also used your Index w Sequence in a Filter to return only the first 10 rows of data as a summary of the data to a job specific page.
However, If there are only 7 rows of data in the filter results, it then returns #REF! in the remailing rows 8,9 &10. Any way to rewrite the formula to eliminate this? Only returning up to 10 rows??
My current formula is
=INDEX(FILTER('WO Labor Index'!$A$3:$A$10000,'WO Labor Index'!$B$3:$B$10000=A2),SEQUENCE(10))
The latest question was duplicated and discussed in another thread.
- Peter,
That did the trick! Thanks for the help.
