Forum Discussion

chastain1337's avatar
chastain1337
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's avatar
    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's avatar
PeterBartholomew1
Silver Contributor
Jan 13, 2021

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?

Resources