CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaspreadsheetflattengoogle-query-language
Wellington Jesus
Wellington Jesus

Reputation: 25

Group many rows with string in google query

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 link description here

Does anyone have any ideas? Thank you very much!

Upvotes: 1

Views: 474

Answers (2)

player0
player0

Reputation: 1

use:

=INDEX(REGEXREPLACE(TO_TEXT(QUERY(SPLIT(FLATTEN(
 IF(COLUMN(1:1)<=C3:C; A3:A&"×"&TEXT(B3:B+COLUMN(1:1)-1; 
 "yyyymmdd\¤d")&"×"&D3:D; )); "×"); 
 "select Col1,max(Col3) 
  where Col2 is not null 
  group by Col1 
  pivot Col2")); "^(.*¤)";))

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

Upvotes: 1

Erik Tyler
Erik Tyler

Reputation: 9345

I've added a new sheet ("Erik Help"). The following single array formula can be found in cell F1:

=ArrayFormula({""\TEXT(SEQUENCE(1;DAY(EOMONTH(B3;0));EOMONTH(B3;-1)+1);"d");SORT(UNIQUE(FILTER(A3:A;A3:A<>"")))\IFERROR(VLOOKUP(VALUE(SORT(UNIQUE(FILTER(A3:A;A3:A<>"")))&SEQUENCE(1;DAY(EOMONTH(B3;0));EOMONTH(B3;-1)+1));SPLIT(UNIQUE(FLATTEN(FILTER(A3:A&(B3:B+IF(SEQUENCE(1;14;0)<C3:C;SEQUENCE(1;14;0)))&"~"&D3:D;A3:A<>"")));"~");2;FALSE))})

The formula produces all left-side labels, all top headers and all grid results.

The formula assumes that all dates in the sheet's Col D will be in the same month/year (based on your posted images running 1-31).

It also assumes that no event will span more than a 14-day period. If any event will exceed 14 days, you'll need to change the 14 in the formula to the maximum possible number of days any one event may span per ID.

It is further assumed that no two "Justificatory" labels will overlap on the same dates per ID.

This formula goes beyond the norm in complexity for this free volunteer-run forum. I trust you'll understand that I don't have the time, in addition to having developed the formula, to also explain it. I encourage you (or others who might be interested) to dig into it, take it apart, and explore independently how it all works.

Upvotes: 3

Related Questions