1

I have data in column A with IDs and a column B with data for those IDs. Right now I have multiple (usually 2-3) rows with the same ID but different data. What formula to use to merge those into one row per ID?

  • Please add a brief description of your search/research efforts as is suggested on How to Ask. – Rubén Jun 19 at 1:41
  • Here is what I have put together link but I don't think I have gone the right direction. I have found this piece of code =if(COUNTIF($A$1:$A1,A1)=1,A1,"") but I wasn't able to rebuild it for my use because that basically just hides the ID whenever that would be a duplicate – Marek Marhoul Jun 19 at 18:16
1

=ARRAYFORMULA({UNIQUE(INDIRECT("A2:A"&COUNTA(A1:A)))\ 
 TRIM(TRANSPOSE(QUERY(TRANSPOSE(IF(QUERY(QUERY(A2:B; 
 "select count(A) where A is not null group by A pivot B"; 0); "offset 1"; 0)<>""; 
 QUERY(A2:B; 
 "select count(A) where A is not null group by A pivot B limit 0"); )); ; 999^99)))})

0

  • In my spreadsheet your formula stacks the output columns below each other AND it seems like it is also assorted. But I cannot find out why. I have watched some videos and I understand how Query works but it is still above my skills to solve it myself for now. Link to my test spreadsheet link – Marek Marhoul Jun 20 at 6:54
  • @MarekMarhoul fixed. answer and your sheet updated – user0 Jun 20 at 10:15
  • 1
    This works perfectly! Thank you very much – Marek Marhoul Jun 20 at 11:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.