0

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. enter image description here

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.

1

=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)))}; 
 ",$"; ))

0

  • 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
  • not sure I follow but try: docs.google.com/spreadsheets/d/… – user0 Aug 29 at 11:09

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.