Combine various activities into one cell when the condition (date) is met google sheet-CodePudding

Good day!

I'm seeking for your kind assistance pls. What is the right formula to arrive the below output? thank you in advance.

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

CodePudding user response：

try:

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

update:

=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)), "♠", ))