Using UNIQUE & FILTER with SPILL ranges

[drluke]

My data is arranged in columns as in A & B below. I am attempting to extract a list based on the name as shown in D & E below. I am using UNIQUE and FILTER functions to extract the data, but what I cannot figure out is how to take the spill range of e.g. Name 1 into consideration in my formula before Name 2 is returned with it's list. Essentially how to skip over rows 2-5 in coumn D and place Name 2 in row 6.

Book1
	A	B	C	D	E	F
1	Name	Reference		Name 1	Ref 1
2	Name 1	Ref 1			Ref 10
3	Name 2	Ref 8			Ref 12
4	Name 3	Ref 2			Ref 9
5	Name 1	Ref 10			Ref 4
6	Name 2	Ref 6		Name 2	Ref 8
7	Name 1	Ref 12			Ref 6
8	Name 3	Ref 3			Ref 13
9	Name 2	Ref 13			Ref 11
10	Name 1	Ref 9
11	Name 3	Ref 7
12	Name 2	Ref 11
13	Name 1	Ref 4
14	Name 3	Ref 5
15
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=FILTER(B2:B14,A2:A14=D1)
E6:E9	E6	=FILTER(B2:B14,A2:A14=D6)
Dynamic array formulas.

Any advice appreciated.

 

[SanjayGMusafir]

Unable to see your data. Use a range in your excel sheet that you want to share and click Mini Sheet to copy

 

[drluke]

I did that already. Here it is again.

Book1
	A	B	C	D	E	F
1	Name	Reference		Name 1	Ref 1
2	Name 1	Ref 1			Ref 10
3	Name 2	Ref 8			Ref 12
4	Name 3	Ref 2			Ref 9
5	Name 1	Ref 10			Ref 4
6	Name 2	Ref 6		Name 2	Ref 8
7	Name 1	Ref 12			Ref 6
8	Name 3	Ref 3			Ref 13
9	Name 2	Ref 13			Ref 11
10	Name 1	Ref 9
11	Name 3	Ref 7
12	Name 2	Ref 11
13	Name 1	Ref 4
14	Name 3	Ref 5
15
Sheet1

Cell Formulas
Range	Formula
E1:E5	E1	=FILTER(B2:B14,A2:A14=D1)
E6:E9	E6	=FILTER(B2:B14,A2:A14=D6)
Dynamic array formulas.

 

[StephenCrump]

You could do it one formula: =LET(s,SORT(A2:B14,1),t,TAKE(s,,1),HSTACK(IF(MATCH(t,t,)=SEQUENCE(ROWS(s)),t,""),TAKE(s,,-1)))

 

[kevin9999]

That's brilliant Stephen