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:
I want the result to be the same as the image below:
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"))