Creating a dynamic list of unique values in a range with a condition

[GuyGadois]

I apologize, but I cannot get XL2BB to work right now. Excel hangs when the Add-on is enabled.

I have the following sheet, where I am trying to create a unique list of values from the values in Column A if the corresponding value in Column B says "Yes." The dynamic list would start in Column C and just contain values in column A if column B equals "Yes". Is there a formula I can use to accomplish this?

Cheers,

Guy

	Component A	Dynamic List
Product 1		Product 3
Product 2		Product 12
Product 3	Yes	Product 21
Product 4		Product 28
Product 5		Product 34
Product 6
Product 7
Product 8
Product 9
Product 10
Product 11
Product 12	Yes
Product 13
Product 14
Product 15
Product 16
Product 17
Product 18
Product 19
Product 20
Product 21	Yes
Product 22
Product 23
Product 24
Product 25
Product 26
Product 27
Product 28	Yes
Product 29
Product 30
Product 31
Product 32
Product 33
Product 34	Yes

 

[Cubist]

The easiest is using a pivot table in 2019.

 

[GuyGadois]

I was hoping to avoid using pivot tables on this sheet. Are there no formulas to accomplish what I am looking to do?

 

[Cubist]

The FILTER function available in 2021 makes it easy but you can try this. It's not entirely dynamic. You'd need to pull down until you get an error.

Book2
	A	B	C	D
1	Prod	Bool		List
2	Product 1			Product 3
3	Product 2			Product 12
4	Product 3	Yes		Product 21
5	Product 4			Product 28
6	Product 5			Product 34
7	Product 6
8	Product 7
9	Product 8
10	Product 9
11	Product 10
12	Product 11
13	Product 12	Yes
14	Product 13
15	Product 14
16	Product 15
17	Product 16
18	Product 17
19	Product 18
20	Product 19
21	Product 20
22	Product 21	Yes
23	Product 22
24	Product 23
25	Product 24
26	Product 25
27	Product 26
28	Product 27
29	Product 28	Yes
30	Product 29
31	Product 30
32	Product 31
33	Product 32
34	Product 33
35	Product 34	Yes
Sheet1

Cell Formulas
Range	Formula
D2:D6	D2	=INDEX($A$2:$A$35,AGGREGATE(15,6,ROW($B$2:$B$35)-ROW($B$2)+1/($B$2:$B$35="Yes"),ROWS($1:1)))