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

Question

I have a sheet that looks something like this:

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.

score 2 · Accepted Answer · answered Dec 19 '19 at 18:26

2

use:

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

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

answered Dec 19 '19 at 18:26

player0

Okay, so that worked, however I've run into another issue. I assume this is because I'm using strings of text, but the remove duplicates isn't working for me: https://i.imgur.com/4BegC22.png – Falcon4ch Dec 19 '19 at 19:18
can you share a copy of your sheet? – player0 Dec 19 '19 at 21:06
Sure - https://docs.google.com/spreadsheets/d/19ctrGxIx_HXom9jeywpiTHmbYnXdKOVs8sOyGARL3EM/edit?usp=sharing – Falcon4ch Dec 19 '19 at 21:21

score 1 · Answer 2 · answered Dec 19 '19 at 16:49

1

See if this works

=join(" " ,Unique(filter(A:A, A:A<>"Mouse")))

Change range to suit.

answered Dec 19 '19 at 16:49

JPV

2 Answers2