Add an extra choice to a dynamic array formula

[Tarver]

I've got a formula that gives me n unique items from a sorted pivot table (starting at $A$6.). I enter the n number of entries I want to see (in cell $C$2), and the list updates:

=UNIQUE($A$6:OFFSET($A$6,$C$2,0))

However, I'd also like to force an additional entry at the bottom of this list, "All Others." So, if I ask for 5 items, I want to see a sixth at the bottom.

Is there a way to modify the existing formula to force that "All Others" entry at bottom?

 

[JGordon11]

Your formula does not work quite as you described. If any items in A6 to A6+n-1 repeat then the unique function will return fewer than n items. In the example below the formula returns 4 (not 5) unique items because B is repeated once in the first five items.

That problem aside you can redimension and add a last row like this:

Book1
	A	B	C	D
1
2			5
3
4
5
6	A		A
7	B		B
8	C		C
9	B		D
10	D		All Others
11	A
12	C
13	E
14	D
15	F
16	B
17	A
18	D
19	F
20
Sheet2

Cell Formulas
Range	Formula
C6:C10	C6	=LET(a, UNIQUE($A$6:OFFSET($A$6,$C$2,0)), s, SEQUENCE(ROWS(a)+1), IF(s<ROWS(s),INDEX(a,s), "All Others"))
Dynamic array formulas.

 

[Tarver]

Never would've come up with that formula, but it works perfectly. Thank you.

You are correct that my formula won't catch a repeat, but since it's applied to a pivot where the values are sorted, that won't be an issue.

Thanks again for your help!