Unique entries, keeping only one with a labelled word

[Jazzzz]

Hello

I'm stuck on the problem below.

I have a dataset that I would like to categorise and filter. In this process I want to (1) remove cells that are not unique, (2) remove cells that contain a specific word from a list, and (3) keep one of those uniques, even though it contains a word from said list.

The idea is, that I will be able to lable entries, ex. "She eats Orange" with "Orange", and an entry like "She eats Orange 12345" wont show up.
If I get new raw data (ex. "The grass is green" and "The grass is green 1234", both should show up, until I label one ex. "Green".


The attached excel document is an attempt to show the desired result.


Thanks
Jonas / Jazzzz

[FlameRetired]

Withdrawn by FR.

[menem]

If you don't mind to use helper columns, please try.

Uniq-check
B6
=IF(COUNTIF($A$5:$A$15,A6)=1,1,IF(COUNTIF(A$5:A6,A6)=1,1,0))

Labeled check
C6
=IFERROR(INDEX(H:H,AGGREGATE(15,6,ROW($H$5:$H$15)/(1/(($H$5:$H$15 < > "")*(FIND(LOWER($H$5:$H$15),LOWER(A6)) > 0))),1)),0)

First of each label
D6
=IF(C6 < > 0,IF(COUNTIF(C$5:C6,C6)=1,1,0),B6)

Selection check
E6
=IF(D6=1,MAX(E$5:E5)+1,0)

Output
F6
=IF(ROWS(F$5:F5) < =MAX($E$5:$E$15),INDEX($A$5:$A$15,MATCH(ROWS(F$5:F5),$E$5:$E$15,0)),"")

Note : Data range from row 5 to 15

Regards.

[Gregb11]

Using similar logic as @menen, but using some 365 formulas, this uses 2 Helper columns:
1st, in cell B6, then copied down:
=IFERROR(FILTER($F$6:$F$8,ISNUMBER(SEARCH($F$6:$F$8,A6))),0)

then in C6 and copied down:
=IF(B6=0,1,COUNTIFS(B$6:B6,B6))

Finally in D6 (don't copy down because this is an array formula)
=FILTER(A6:A14,C6:C14=1)

(see attached)

[Jazzzz]

Thanks a lot. Both solutions worked as they should

Thanks
Jonas / Jazzzz