0

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?

0

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"))

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.