excel - 满足条件(日期)时将各种事件合并到一个单元格中 google sheet

Question

再会!
我正在寻求您的帮助。得出以下输出的正确公式是什么？先感谢您。

Data
Ref   Activity 1   Activity 2   Activity 3
001  23-Oct-2021   25-Oct-2021  5-Nov-2021
002  25-Oct-2021   30-Oct-2021  5-Nov-2021
003  25-Oct-2021   5-Nov-2021   6-Nov-2021

Output
  23-Oct-2021        
001 - Activity 1  

 25-Oct-2021         
001 - Activity 2    
002 - Activity 1    
003 - Activity 1   

Answer 1

尝试:

=INDEX(QUERY(FLATTEN(SPLIT(QUERY(FLATTEN(TRANSPOSE(QUERY(SPLIT(FLATTEN(
 IF(B2:D="",," ♣"&TO_TEXT(B2:D)&"♦"&A2:A&" - "&B1:D1)), "♦"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"))), 
 "where Col1 is not null"), "♣")), 
 "where Col1 is not null offset 1", 0))

更新:

=INDEX(SUBSTITUTE(SUBSTITUTE(TRIM(TRANSPOSE(SPLIT(FLATTEN(
 QUERY(SUBSTITUTE(QUERY(SPLIT(FLATTEN(
 IF(C4:E6="",,TO_TEXT(C4:E6)&"♠♣♦"&B4:B6&" - "&C3:E3)), "♦"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"), 
 " ", CHAR(13)),,9^9)), "♣"))), " ", CHAR(10)), "♠", ))