Google Sheets: How to combine multiple rows in their own cells according to an ID?

Question

I tried searching for this question very hard, and only managed to find one solution that sort of works. Here's the reference: how to merge rows together if duplicate id (google spreadsheet)

His question is exactly the same as mine, and the solution partially works because sometimes google sheets have an error with custom functions and the cell just get stuck on "Loading.."

I am writing this question in hopes of being able to achieve the same results, without a custom function, using sheets native formulas.

Here's another reference to something similar to what I need: How to combine rows with same ID in Google Sheets?. It is only similar because I need the combined data to be in their own cells, and not merged into one cell.

In other words, the input:

Col A(ID)     Col B(values)     Col C(values)     
1               sample1            sample2           
2               sample2            sample4           
1               newsample1         newsample2        

Expected output:

Col A(ID)     Col B(values)     Col C(values)     Col D(values)     Col E(values)   
1               sample1            sample2         newsample1         newsample2  
2               sample2            sample4           

Essentially, again it is exactly the same requirements as how to merge rows together if duplicate id (google spreadsheet), but looking for a solution using google native formula rather than a custom formula if possible.

Answer 1

try:

=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(SPLIT(TRIM(FLATTEN(QUERY(QUERY(SPLIT(
 FLATTEN(A1:A5&"×"&ROW(B1:C5)&COLUMN(B1:C5)&"¤"&SUBSTITUTE(B1:C5, " ", "♦")), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9))), 
 " ")&"", "(^.+¤)", ), "♦", " "))

fix:

=ARRAYFORMULA(SUBSTITUTE(REGEXREPLACE(SPLIT(TRIM(FLATTEN(QUERY(QUERY(SPLIT(
 FLATTEN(A3:A&"×"&TEXT(ROW(B3:H), "000000")&TEXT(COLUMN(B3:H), "000000")&"¤"&
 SUBSTITUTE(B3:H, " ", "♦")), "×"), 
 "select max(Col2) where Col2 is not null group by Col2 pivot Col1"),,9^9))), 
 " ")&"", "(^.+¤)", ), "♦", " "))