# Automatic merging of duplicate cells under common ID

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

``````=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)))})`````` • 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
• This works perfectly! Thank you very much – Marek Marhoul Jun 20 at 11:43
• Quick question about this formula - is it possible to merge exactly like this but with a comma and space instead of just space? – Marek Marhoul Aug 26 at 16:51
• @MarekMarhoul sure: `=ARRAYFORMULA(REGEXREPLACE(TO_TEXT({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)))}); ",\$"; ))` - i.stack.imgur.com/Ahlip.png – user0 Aug 29 at 0:17