An Arrayformula to Split the given data as shown-CodePudding

Please tell me the Arrayformula at C1 which converts the column A to column C,D & E as shown.

"Extra Information" Sheet:

This Arrayformula at B1 is what I achieved, I am not able to split this by ^ into columns as shown in 1st Image.

CodePudding user response：

try in C2:

=ARRAYFORMULA(IFERROR(REGEXREPLACE(REGEXREPLACE({
 VLOOKUP(A2:A, TRIM(SPLIT(FLATTEN(QUERY(QUERY({IF(Sheet1!A2:D="",,{Sheet1!A2:A&"♦", Sheet1!B2:D&"♥"}), ROW(Sheet1!A2:A)}, 
 "select max(Col2) where Col2 is not null group by Col5 pivot Col1"),,9^9)), "♦")), 2, ), 
 VLOOKUP(A2:A, TRIM(SPLIT(FLATTEN(QUERY(QUERY({IF(Sheet1!A2:D="",,{Sheet1!A2:A&"♦", Sheet1!B2:D&"♥"}), ROW(Sheet1!A2:A)}, 
 "select max(Col3) where Col2 is not null group by Col5 pivot Col1"),,9^9)), "♦")), 2, ), 
 VLOOKUP(A2:A, TRIM(SPLIT(FLATTEN(QUERY(QUERY({IF(Sheet1!A2:D="",,{Sheet1!A2:A&"♦", Sheet1!B2:C&"♥", TEXT(Sheet1!D2:D, "dd/mm/e")&"♥"}), ROW(Sheet1!A2:A)}, 
 "select max(Col4) where Col2 is not null group by Col5 pivot Col1"),,9^9)), "♦")), 2, )}, "♥$", ), "♥ ", CHAR(10))))