Google-sheets – Get data from specific date in Google Sheets

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.

Following up OP's Comment:

This is better: https://stackoverflow.com/questions/42869218/data-transformation-in-google-sheets

copy of formula provided by @Rubén:

=QUERY({"Group Leader","Groupmate","Gender";A2:C;A2:A,D2:E;A2:A,F2:G},"select * where Col3 <> '' order by Col1 asc")

with my addition of order by Col1 asc.