How to create a week calendar sheet in Google Sheets using Array Formulas?

1
votes

I have a "DB" sheet with entries, dated with week numbers:

NAME   | ACTION  | WEEK
------------------------
carrot | harvest | 48
carrot | wash    | 48
garlic | plant   | 49

(Columns are set as named ranges for convenience.)

I want to automatically generate a weekly action calendar on a second sheet as follows:

(A)       || (B) | (C)     | (D)   | (E) 
-----------------------------------------
NAME / WK || 47  | 48      | 49    | 50 | (1)
=========================================
carrot    ||     | harvest |       |    | (2)
          ||     | wash    |       |    | 
-----------------------------------------
garlic    ||     |         | plant |    | (3)

If I would use the manual interpolation, I would put a formula like this in the value cells (B2:E3) in the example above:

=IFNA(JOIN(CHAR(10), FILTER(ACTIONS, WEEK=B$1, NAME=$A2)), "")

(This specific formula would go in the cell B2.)

However, I do not want to use manual interpolation in this calendar: When I add, change or remove rows in the DB sheet, the calendar sheet should update automatically, without me changing anything there.

Is there a way to do this by putting a single formula in (say) the B2 cell, instead of filling every cell in the B2:E3 range manually? (Let's say I already fill A:A and 1:1 automatically, but that could be moved inside that formula if necessary.)

2
google-sheetsgoogle-sheets-formulaarray-formulas

2 Answers

3
votes

try:

=ARRAYFORMULA(QUERY(TRIM(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(QUERY(
 {A2:A&"♦", B2:B, "♦"&C2:C, CHAR(10)&B2:B}, 
 "select Col1,max(Col4),Col3 
  where Col2 is not null 
  group by Col1,Col3 
  pivot Col2"), "offset 1", 0)),,9^9)), "♦")), 
 "select Col1,max(Col2) 
  group by Col1 
  pivot Col3
  label Col1'NAME / WK'"))

enter image description here

1
votes

Well it took me much longer, and I have a much uglier formula that I won't even try to optimise now, but in case anyone wants to consider a different way to do it, compared to player0's solution, here is another formula:

=ArrayFormula(query(
 {trim(split(transpose(query(
   { query(   query({A2:A & "~" & C2:C & "~", "♦" & B2:B},
                "select max(Col2) where Col1<>'~~' group by Col2 pivot Col1",0),"limit 0",1);
     substitute(regexreplace(trim( 
       query(
        query(query({A2:A & "~" & C2:C & "~", "♦" & B2:B},
                "select max(Col2) where Col1<>'~~' group by Col2 pivot Col1",0),"offset 1",0),,99^99)
       ), "^♦","" ),"♦",char(10))
   },,99^99)),"~",1,0))
 },"select Col1, max(Col3) group by Col1 pivot Col2",0))

Maybe not using FLATTEN was a contributing factor for the convoluted design...

enter image description here