Forum Discussion

Copper Contributor

Dec 10, 2019

Solved

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 f...

excel

Formulas and Functions

PeterBartholomew1
Dec 10, 2019
chastain1337

= INDEX( UNIQUE(ColumnA), SEQUENCE(10) )
would return 10 values and
= INDEX( UNIQUE(ColumnA), SEQUENCE(1,10) )
would transpose the result array.

PeterBartholomew1

Silver Contributor

Dec 10, 2019

chastain1337

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

would return 10 values and

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

would transpose the result array.

Cohutta68
Copper Contributor
Jan 12, 2021
PeterBartholomew1

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))
- SergeiBaklan
  Diamond Contributor
  Jan 13, 2021
  Cohutta68
  
  The latest question was duplicated and discussed in another thread.
chastain1337
Copper Contributor
Dec 11, 2019
Peter,

That did the trick! Thanks for the help.