# Merging cells based on same ID but divided into categories (columns)

I receive a pretty extent list of material that has been delivered and every single product is in a separate row. That means for one order I have easily a lot over 15 rows and I need to merge the products for further data processing.

Following image depicts the final output I am looking for.

I am using various formulas and steps to achieve this, one of them being a formula I got as an answer to my very similar step from a previous question:

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

This seems to be the part that isn't working properly but I cannot find out why. Also, my solution is unnecessarily complicated (too many steps that could be solved in fewer formulas I bet)

My spreadsheet is here with some explanation on every sheet. Thanks for any help or advice.

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

• This is exactly what I had in mind when saying it could be a lot simpler and it is working perfectly. If I am correct, it outputs codes in column B into B because they are in B, right? Not because the column F ("Destination column") contains that information it should be in B. Is that function reasonably simple to accomplish or would that mean a complete rework of the formula you posted? – Marek Marhoul Aug 29 at 8:59