Home > Back-end >  Group many rows with string in google query
Group many rows with string in google query

Time:11-04

I have a problem with my google query. I need to group several lines and some are repeated.

At row F2 I have the =ArrayFormula(query(TO_TEXT({A3:A\SE(G1:AK1>=B3:B;SE(G1:AK1<=B3:B C3:C-1;D3:D;"*");"*")});"SELECT * where Col1 IS NOT NULL ORDER BY Col1")) the result is:

enter image description here

I want the result to be the same as the image below:

enter image description here

Link to sheets: enter image description here


to force dates in row 1:

=ARRAYFORMULA({IFERROR(QUERY(QUERY(SPLIT(FLATTEN(
 IF(COLUMN(1:1)<=C3:C; A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×"); 
 "select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "limit 0 label Col1'xx'"; 1)*1);
 QUERY(QUERY(SPLIT(FLATTEN(
 IF(COLUMN(1:1)<=C3:C; A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×"); 
 "select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "offset 1"; 0)})

update:

=ARRAYFORMULA(IFERROR({IFERROR(QUERY(QUERY(SPLIT(FLATTEN(
 IF((MONTH(B3:B)=MONTH(D1))*(COLUMN(1:1)<=C3:C); A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×"); 
 "select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "limit 0 label Col1'xx'"; 1)*1);
 QUERY(QUERY(SPLIT(FLATTEN(
 IF((MONTH(B3:B)=MONTH(D1))*(COLUMN(1:1)<=C3:C); A3:A&"×"&B3:B COLUMN(1:1)-1&"×"&D3:D; )); "×"); 
 "select Col1,max(Col3) where Col2 is not null group by Col1 pivot Col2"); "offset 1"; 0)}; "no data"))

enter image description here

  • Related