An Arrayformula to Split the given data as shown

user_8866238 0
Viewed 40

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

Google Sheet Link

Extra information:
Column A is actually not raw data, it is also an Arrayformula:

=ARRAYFORMULA(VLOOKUP($A:$A, TRIM(SUBSTITUTE(SPLIT(FLATTEN(QUERY(QUERY( {Sheet1!$B:$B&"^"&Sheet1!$C:$C&"^"&Sheet1!$D:$D&"#", Sheet1!$A:$A,Sheet1!$A:$A&"×"}, "select max(Col1) where Col2 is not null group by Col1 pivot Col3",1),,9^9)), "×"), "#", CHAR(10))), 2, 0))

You can check the "Sheet1" & "Extra Information" sheets to understand it.

"Sheet1" Sheet:

enter image description here

"Extra Information" Sheet:

enter image description here

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

vlookup array-formulas google-sheets-formula google-sheets split
1 Answers

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

enter image description here

user_56326290
Related