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

Question

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.)

Answer 1

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'"))