I tried different approaches to accomplish what I am looking for and it might not be possible with how I have formated my data but I will try to explain it to see if there is a way.

My origin of data looks like the following:

What I want is to be able to extract the HELP-* into another column without duplicate values and one after another. The result I am looking for is from the above table been able to have this:

Is there a way to do this in Google Sheets?

Thank you,

CodePudding user response：

Try this in Google Sheets

=sort(array_constrain(flatten(B2:C),counta(B2:C),1))

CodePudding user response：

Alternatively you can use:

=UNIQUE(QUERY(FLATTEN(B2:C),"where Col1 like 'HELP-%'"))

The QUERY() will now only return those values that start with 'HELP-' in the case you might have other string-values.

A littel more specific even, could be to use:

=UNIQUE(QUERY(FLATTEN(B2:C),"where Col1 matches 'HELP-\d '"))

Where 'matches' will now use the regular expression to only return values that start with 'HELP-' but end with any 1 digits.