Labeling a Unique/Distinct value not including any duplicates then pulling a list of those values

[willow1985]

I am looking for a formula that will mark what items on a list that are completely unique and do not have a duplicate (see below)

Book1
	A	B	C	D	E	F
1	Cost	Item	Unique Y/N		Unique List	Cost
2	$2.00	Apple	N		Corn	$0.56
3	$2.00	Apple	N		Strawberry	$5.00
4	$3.00	Pear	N		Banana	$1.50
5	$3.00	Pear	N
6	$2.50	Broccoli	N
7	$0.56	Corn	Y
8	$2.00	Apple	N
9	$5.00	Strawberry	Y
10	$3.00	Pear	N
11	$1.50	Banana	Y
12	$2.50	Broccoli	N
Sheet1

After which I would like a formula to pull a list of those unique values.
(Formulas needed are in yellow)

Any help with this would be greatly appreciated, thank you!

(Note: the original data will not be names of fruit/veggies but part numbers with a mix of numbers and characters if this matters, example 61300 or PT55522)

 

[Fluff]

Do you need col C, you can use this in E2 & it will return what you want

=UNIQUE(B2:B12,,1)

 

[willow1985]

Do you need col C, you can use this in E2 & it will return what you want

=UNIQUE(B2:B12,,1)

Yes I do need col C as well please.
Thank you very much for D! Out of curiosity is it possible to add a filter to D as well? (see below)

Book1
	A	B	C	D	E	F
1	Cost	Item	Unique Y/N	Type	Unique Vegetables	Cost
2	$2.00	Apple	N	Fruit	Corn	$0.56
3	$2.00	Apple	N	Fruit
4	$3.00	Pear	N	Fruit
5	$3.00	Pear	N	Fruit
6	$2.50	Broccoli	N	Vegetable
7	$0.56	Corn	Y	Vegetable
8	$2.00	Apple	N	Fruit
9	$5.00	Strawberry	Y	Fruit
10	$3.00	Pear	N	Fruit
11	$1.50	Banana	Y	Fruit
12	$2.50	Broccoli	N	Vegetable
Sheet1

 

[Fluff]

Formula for c is

=IF(COUNTIFS(B2:B12,B2:B12)=1,"Y","N")

This goes in C2 only.

What is the criteria for the filter?

 

[willow1985]

Formula for c is

=IF(COUNTIFS(B2:B12,B2:B12)=1,"Y","N")

This goes in C2 only.

What is the criteria for the filter?

if the type in D is Vegetable

 

[Fluff]

How about

=FILTER(B2:B12,(C2:C12="Y")*(D2:D12="Vegetable"))

 

[willow1985]

How about

=FILTER(B2:B12,(C2:C12="Y")*(D2:D12="Vegetable"))

Sorry I think there was a miscommunication. Is there any way to do a filter with the =UNIQUE(B2:B12,,1) formula for vegetables? Only provide a unique list for anything that is a Vegetable?

 

[Fluff]

Not directly, no.

 

[willow1985]

Not directly, no.

I see.
Thank you. I have one more question about a formula you provided: =IF(COUNTIFS(B2:B12,B2:B12)=1,"Y","N")

Would this formula work in a table? The data I am applying it to is in a table with a lot of lines of data and it seems to be freezing or having a SPILL error.

I modified it to: IF(COUNTIFS([Part Number],[Part Number])=1,"Y","N")

 

[Fluff]

No, you cannot use spill ranges in a table, you would need to use

IF(COUNTIFS([Part Number],[@Part Number])=1,"Y","N")