Google-sheets – 1) get unique names AND 2) strip out vacancy AND 3) alpha sort AND 4) append Last name, first name

The where clause supports several types of string matching.

Method 1: String comparison with >= and <

The query string

select A where A < 'G'

selects all strings that would precede G in a dictionary, i.e., all that begin with letters A-F. This is case-sensitive. The case-insensitive form is

select A where lower(A) < 'g'

More complex examples

select A where lower(A)<'cp' 

selects in the range a-co.

select A where lower(A) >= 'cp' and lower(A) < 'hb'

selects the range cp-ha.

Method 2: regular expressions

The query string

where B matches '^[A-F].*'

selects the rows where the content of B begins with a letter from A to F. (The regular expression means: ^ beginning of line, [A-F] one character in this range, .* other characters may follow.)

The match is case-sensitive. Its case-insensitive version would be

where lower(B) matches '^[a-f].*'

More complex examples

Matching strings in alphabetic range "a" to "co".

where lower(B) matches '^([ab]|c[a-o]).*'

Here | separates alternatives. Beginning with a or b is okay. So is beginning with c, but only if followed by a letter in range a-o.

Matching the range cp-ha:

where lower(B) matches '^(c[p-z]|[d-g]|ha).*'

There are three alternatives here: c followed by p-z, anything beginning with d-g , and ha.

The problem is that split does not work with an array (unfortunately). There are workarounds though. In your case, replacing index-split with regexextract does the job:

=FILTER(A19:A32, NOT(COUNTIF(regexextract(B:B, "^\S+"), A19:A32)))

The regular expressiion ^\S+ means: get all non-whitespace characters at the beginning of the string, which is what you tried to do.

Aside: you have two Miguels on your list, so ignoring last names might not be such a good idea. What if one Miguel responds and the other doesn't?