Filter & Sequence Together

[BrettOlbrys1]

Is it possible to use Filter & Sequence together in one formula?

I have 1,000 rows of data in columns A, B, C, and D.

Column A = Customer Name
Column B = Market Segment
Column C = Qty
Column D = Product

I want to return the Top 5 rows of data based on QTY (greatest to least) and limit it to 5 rows, even if there are duplicate qty's. Right now, I have the following formula:

D2 = Product Y

SORT(FILTER(CSR_Activations,(CSR_Activations[Qty]>=(LARGE(IF(CSR_Activations[Product]=D2,CSR_Activations[Qty]),5)))*(CSR_Activations[Product]=D2)),3,-1)

When there are duplicate qty's that bring my total line count to greater than 5, I get a SPILL error because I have other tables below this section. I know with the SEQUENCE command I can limit it to 5 rows, but I have been unable to combine the two functions so that I only return the Top 5 rows with data in columns A, B, C, and D.

 

[BrettOlbrys1]

I figured out that this formula works:

INDEX(SORT(FILTER(CSR_Activations,(CSR_Activations[Qty]>=(LARGE(IF(CSR_Activations[Product]=D2,CSR_Activations[Qty]),5)))*(CSR_Activations[Product]=D2)),3,-1),SEQUENCE(5),{1})

But, I need to have this formula in each column and change the last "1" to a "2", "3", and "4" for columns A, B, C, and D.

Question, is there a formula that doesn't require me to change the last number?

 

[StephenCrump]

Perhaps along these lines?

	A	B	C	D	E	F
1		Data			Filter	5
2		q	1		r	99
3		w	15		g	88
4		a	2		t	77
5		s	44		s	44
6		d	44		d	44
7		f	44
8		g	88
9		r	99
10		t	77
11
Sheet1

Cell Formulas
Range	Formula
E2:F6	E2	=LET(d,B2:C10,N,F1,s,SORT(d,2,-1),FILTER(s,SEQUENCE(ROWS(d))<=N))
Dynamic array formulas.

Edit: Or a little more succinctly: =LET(N,F1,s,SORT(B2:C10,2,-1),FILTER(s,SEQUENCE(ROWS(s))<=N))