I would like to know if and how I can filter a list of values which was already created by the unique() function?

Example:

Suppose I have a column of values with duplications.

``````Abc 433
Gal 653
Dov 123
Gal 653
Dov 124
``````

I operated Unique function on them to receive:

``````Abc 433
Gal 653
Dov 123
Dov 124
``````

and now I need to have all the values which does not contain Gal for example. So, my final column should be:

``````Abc 433
Dov 123
Dov 124
``````

Until now I tried variations of the following:

`=UNIQUE(FILTER(A1:A50), "<>Gal*"))`

How can I achieve that?

This should work for you:

`=UNIQUE(FILTER(A1:A50,A1:A50<>"",NOT(REGEXMATCH(A1:A50,"Gal"))))`

If your letter sequence may vary in capitalization (e.g., if it may show up in various places as "Gal" or "GAL" or "gal"), use this:

`=UNIQUE(FILTER(A1:A50,A1:A50<>"",NOT(REGEXMATCH(UPPER(A1:A50),"GAL"))))`

You could also go a different route with the same result:

`=UNIQUE(FILTER(A1:A50,A1:A50<>"",NOT(ISNUMBER(SEARCH("Gal",A1:A50)))))`

If the form of your values is always three letters, a space and then some numbers, you could do it this way:

`=UNIQUE(FILTER(A1:A50,A1:A50<>"",LEFT(A1:A50,3)<>"Gal"))`

• Thank you Erik, I will check you suggestion on Sunday at office. Jan 14, 2022 at 16:27