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