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