So I have a set of data here, example below

and I am trying to get a result similar to this

I am thinking of using query, and I found this query formula:

=query({Sheet1!$A$1:$A;Sheet1!$B$1:$B;Sheet1!$C$1:$C},"select * where Col1 <>'' ",0)

but this only applies in transforming wide to long dataset

Is there a way to do it using query? or even an app script?

CodePudding user response：

What you can do is to add a set of values with only the first two words. For that you can use:

=INDEX(SPLIT(UNIQUE(IFNA(REGEXEXTRACT(A:A,"(.  . ) "))&"¿¿"),"¿",1,0))

PS: the two ¿¿ are for adding the extra columns. If you actually have more columns, add more ¿ signs -- (you don't need to do this step, I'm just showing you the process)

Then, with the query:

=QUERY({A:C;INDEX(SPLIT(UNIQUE(IFNA(REGEXEXTRACT(A:A,"(.  . ) "))&"¿¿"),"¿",1,0))},"Select * where Col1 is not null order by Col1")

You'll get something like this in the right:

Then, with conditional formatting you can get something like this:

=($I1<>"")*($J1="")*($K1="")

PS: adapt the actual columns