I'm trying to achive an hard try in google sheet.

Let's start from what I right now, the A structure in the image.

What I would like to achieve using functions like =QUERY, is the B or C (whatever is fine for me) structore.

Can u help me with the sintax?

I appreciate it so much and thank you very much

Luco

I tried a couplo of function but can't get to the point using QUERY function, maybe I'm using bad syntax.

CodePudding user response：

I don't know if there's an easy way to do it with just one query or filter. What I did is to create the first column with this approach: I joined all the hours column with ", " as separator. Then splitted it again by "," as a result we have all the values listed, then set them as unique:

=unique(arrayformula(trim(transpose(split((join(", ",filter(B:B,B:B <> ""))),",",true,true)))))

Then in the next column a query with byrow to be repeated in each row which finds if column B contains that time:

=byrow(D2:D,lambda(each,join(", ",if(each="","",transpose(query(A:B,"Select A where B contains '"&each&"'"))))))

all in one C variant:

=INDEX(LAMBDA(x, REGEXREPLACE(TRIM(SPLIT(FLATTEN(QUERY(QUERY(SPLIT(FLATTEN(
 TRIM(SPLIT(OFFSET(x,,1), ",")&"×")&"​"&x&","), "​"), 
 "select max(Col2) where Col1 <> '×' and Col2 is not null 
  group by Col2 pivot Col1"),,9^9)), "×")), ",$", ))
 (A2:INDEX(A:A, MAX(ROW(A:A)*(A:A<>"")))))