2

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.

player0
  • 124,011
  • 12
  • 67
  • 124
0x00n0b0dy
  • 25
  • 1
  • 7

1 Answers1

0

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))), 
 " ")&"", "(^.+¤)", ), "♦", " "))

enter image description here

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))), 
 " ")&"", "(^.+¤)", ), "♦", " "))
player0
  • 124,011
  • 12
  • 67
  • 124
  • Hey! Thanks for reply to my question! Your solution does work! However, I notice when I add empty columns to the left of column A, and push all of the old data towards the right, the formula changes to sequence of data, and does not follow the original table data set. Is there a way to ensure that the sequence is followed as well, even if more columns are added to the left of A? Right now it works perfectly only if the table starts from column A and onwards. I hope you understood what I meant! – 0x00n0b0dy Mar 03 '22 at 15:55
  • @0x00n0b0dy not rly sure whats your issue. I just tried it and it works. I selected column A right-clicked and inserted a new column to the left of A and the formula still works as it should. maybe this is an issue of a larger data sample (?) can you share a copy of your sheet? – player0 Mar 04 '22 at 02:42
  • I suspect you're right. It is an issue of a larger data sample most probably. Here's a link to the dataset and comments I've annotated on the sheets as well [Sheets](https://docs.google.com/spreadsheets/d/1BNT08AbdjrYzg_N3YkpyjInToQclhGnRd5M7qXnLdh8/edit?usp=sharing). May you assist in taking a look please? I really appreciate your help!! – 0x00n0b0dy Mar 05 '22 at 11:30
  • @0x00n0b0dy fixed. see your sheet – player0 Mar 05 '22 at 13:27