UNIQUE List With Criteria

[excelbytes]

On the attached is a list of orders and line items. Based on certain criteria, column C indicates whether to include it in the list of Orders in column D or not. What would be the formula to generate a unique list of orders in column D that exclude any order that has a "No" associated with it in column C?

 

[Herakles]

On the attached is a list of orders and line items. Based on certain criteria, column C indicates whether to include it in the list of Orders in column D or not. What would be the formula to generate a unique list of orders in column D that exclude any order that has a "No" associated with it in column C?

View attachment 100330

Here you go. You will need to change the ranges as appropriate.

Multi-level Bill Of Materials explosion.xlsm
	A	B	C	D
1	Sales Order #	Line #	Include	Orders
2	272043	1	Yes	272134
3	272043	2	Yes	272166
4	272043	3	Yes
5	272043	4	Yes
6	272043	5	No
7	272134	1	Yes
8	272166	2	Yes
9	272166	3	Yes
10	272166	4	Yes
UNIQUE

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(FILTER(A2:A10,NOT(ISNUMBER(XMATCH(A2:A10,FILTER(A2:A10,C2:C10="No"))))))
Dynamic array formulas.

 

[excelbytes]

Thanks, I'll try it!

 

[excelbytes]

Works perfectly, Thanks!

 

[Peter_SSs]

You could also try this bit shorter structure.

23 10 14.xlsm
	A	B	C	D
1	Sales Order #	Line #	Include	Orders
2	272043	1	Yes	272134
3	272043	2	Yes	272166
4	272043	3	Yes
5	272043	4	Yes
6	272043	5	No
7	272134	1	Yes
8	272166	2	Yes
9	272166	3	Yes
10	272166	4	Yes
List

Cell Formulas
Range	Formula
D2:D3	D2	=UNIQUE(FILTER(A2:A10,COUNTIFS(A2:A10,A2:A10,C2:C10,"No")=0))
Dynamic array formulas.