Search code examples
regexgoogle-sheetsfilteruniquetextjoin

Concatenating a range while removing duplicate values as well as specific values

I have a sheet that looks something like this:

enter image description here

What I'd like to be able to do is concatenate this range while removing duplicate values, but also excluding certain other values.

For example: I'd like to be able to see cat once (note it is in the sheet twice), but not see Mouse at all (also in the sheet twice).

It would also be great if I could do this while also adding a space in between each concatenated value.

Solution

  • use:

    =TEXTJOIN(" ", 1, UNIQUE(FILTER(A:A, NOT(REGEXMATCH(LOWER(A:A), "mouse")))))

    0

    if you want to exclude more values do:

    =TEXTJOIN(" ", 1, UNIQUE(FILTER(A:A, NOT(REGEXMATCH(LOWER(A:A), "mouse|tree")))))

    if your dataset contains numbers do:

    =TEXTJOIN(" ", 1, UNIQUE(FILTER(A:A, NOT(REGEXMATCH(TO_TEXT(LOWER(A:A)), 
 "mouse|tree|1238")))))