Limiting the length of results from Dynamic / Spill Arrays

[chastain1337]

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?

[#2 · Accepted solution]

@chastain1337 

= INDEX( UNIQUE(ColumnA), SEQUENCE(10) )

would return 10 values and 

= INDEX( UNIQUE(ColumnA), SEQUENCE(1,10) )

would transpose the result array.

[#3]

Peter,

That did the trick! Thanks for the help.

[#4]

@Peter Bartholomew 

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))

[#5]

@Cohutta68 

The latest question was duplicated and discussed in another thread.

[#6]

@chastain1337 

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?

[#7]

@Peter Bartholomew , I found the thread Help limiting length results of results from Filter Formula without #REF! - Microsoft Tech Community