2

For most orders, I have multiple deliveries under the same recipient ID and I need to compare whether everything ordered is really delivered. All products are divided by a comma and have always the same format. Only thing is there are 4 main types of products divided into 4 columns

The result should either be an OK, or an output what and how much is missing

If Excel is easier/better/needed for this job, I can process all the data in Excel without a problem, I just need that thing working somehow :-)

Here is a link to a dummy sheet

  • so you want OK in H column if all products from B,C,D,E are delivered under same ID? – user0 Feb 20 at 10:37
  • Yes, exactly like you said. Also all products in B are always only in B nowhere else, the same with C, D, and E. – Marek Marhoul Feb 20 at 10:59
  • Delivered!A3 has same ID as A2 is that as is or a typo that its not joined under same id? – user0 Feb 20 at 13:07
  • every row is a separate delivery for the same recipient so every time the recipient ID appears all those rows need to be combined. The issue is that for one order there usually are 2-3 separate part deliveries. Also for ordered 3 pieces of something, in one delivery there could be 1 piece delivered and the rest 2 could be delivered in another. – Marek Marhoul Feb 20 at 13:20
1

EU syntax:

=ARRAYFORMULA(IFERROR(TEXTJOIN(", "&CHAR(10); 1; CONCAT(CONCAT(
 INDEX(QUERY({QUERY(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); "select Col1")\
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")-
 ARRAY_CONSTRAIN(IFERROR(VLOOKUP(IFERROR(VLOOKUP(
 INDEX(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"); ; 1);
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); 1; 0); 0); 
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); 2; 0); 0); COUNTA(
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"))/2; 1)}; 
 "select Col2,'x',Col1 where Col2>0 label 'x'''"); ; 1);
 INDEX(QUERY({QUERY(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); "select Col1")\
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")-
 ARRAY_CONSTRAIN(IFERROR(VLOOKUP(IFERROR(VLOOKUP(
 INDEX(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"); ; 1);
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); 1; 0); 0); 
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); 2; 0); 0); COUNTA(
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"))/2; 1)}; 
 "select Col2,'x',Col1 where Col2>0 label 'x'''"); ; 2));
 INDEX(QUERY({QUERY(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); "select Col1")\
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")-
 ARRAY_CONSTRAIN(IFERROR(VLOOKUP(IFERROR(VLOOKUP(
 INDEX(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"); ; 1);
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); 1; 0); 0); QUERY(
 IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( ; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&
 CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"); 2; 0); 0); COUNTA(
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "x(.+)")\
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); "\d+"))}); 
 "select Col1, sum(Col2) where Col1 is not null Group by Col1"))/2; 1)}; 
 "select Col2,'x',Col1 where Col2>0 label 'x'''"); ; 3))); "OK"))

US syntax:

=ARRAYFORMULA(IFERROR(TEXTJOIN(", "&CHAR(10), 1, CONCAT(CONCAT(
 INDEX(QUERY({QUERY(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), "select Col1"),
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")-
 ARRAY_CONSTRAIN(IFERROR(VLOOKUP(IFERROR(VLOOKUP(
 INDEX(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), , 1),
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), 1, 0), 0), 
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), 2, 0), 0), COUNTA(
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"))/2, 1)}, 
 "select Col2,'x',Col1 where Col2>0 label 'x'''"), , 1),
 INDEX(QUERY({QUERY(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), "select Col1"),
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")-
 ARRAY_CONSTRAIN(IFERROR(VLOOKUP(IFERROR(VLOOKUP(
 INDEX(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), , 1),
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), 1, 0), 0), 
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), 2, 0), 0), COUNTA(
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"))/2, 1)}, 
 "select Col2,'x',Col1 where Col2>0 label 'x'''"), , 2)),
 INDEX(QUERY({QUERY(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), "select Col1"),
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''")-
 ARRAY_CONSTRAIN(IFERROR(VLOOKUP(IFERROR(VLOOKUP(
 INDEX(QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1 label sum(Col2)''"), , 1),
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), 1, 0), 0), QUERY(
 IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN( , 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), 
 SUBSTITUTE(SPLIT(CONCATENATE(FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&
 CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null group by Col1"), 2, 0), 0), COUNTA(
 QUERY(IFERROR({REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "x(.+)"),
 VALUE(REGEXEXTRACT(
 TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")), "\d+"))}), 
 "select Col1, sum(Col2) where Col1 is not null Group by Col1"))/2, 1)}, 
 "select Col2,'x',Col1 where Col2>0 label 'x'''"), , 3))), "OK"))

  • I might need a little bit of help with making it fit to my spreadsheet, I will message you on your test sheet. Thanks – Marek Marhoul Mar 5 at 6:39
  • 1
    This formula is working flawlessly! Everything works automatically (just takes time, of course since it is a complicated formula). The main thing is you have created exactly what I was looking for – Marek Marhoul Mar 6 at 6:40
0

NOTE: This solution works only with unique items

US syntax:

=IF(ARRAYFORMULA(IFERROR(SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1, 
 SUBSTITUTE(B2:E2, ", ", ",")), ",")); TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))}, 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0), 
 NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")); 
 TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))}, 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0), 
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))))), "♂", ""), ))="", 
 "OK", ARRAYFORMULA(textJOIN(", ", 1, SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1, 
 SUBSTITUTE(B2:E2, ", ", ",")), ",")); TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))}, 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0), 
 NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")); 
 TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))}, 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0), 
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
 {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
 "×"), "  ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))))), "♂", ""))))

enter image description here

EU syntax:

=IF(ARRAYFORMULA(IFERROR(SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1; 
 SUBSTITUTE(B2:E2; ", "; ",")); ",")); TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))}; 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0); 
 NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); 
 TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))}; 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0); 
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))))); "♂"; ""); ))=""; 
 "OK"; ARRAYFORMULA(textJOIN(", "; 1; SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1; 
 SUBSTITUTE(B2:E2; ", "; ",")); ",")); TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))}; 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0); 
 NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")); 
 TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
 COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))}; 
 "limit "&COUNTA(TRANSPOSE(
 SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0); 
 TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
 {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0)\ SUBSTITUTE(SPLIT(CONCATENATE(
 FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
 "×"); "  "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))))); "♂"; ""))))
  • If I am correct it is not possible to advance the range (amount of deliveries) of this formula easily, right? For example if I need to compare 30 deliveries instead of this exact number that is in the dummy spreadsheet, can I still make it work? – Marek Marhoul Feb 20 at 18:57
  • sure you can, just edit every: $B$2:$E, B2:E2 and Delivered!$B$2:$E range... let's say for: $B$2:$Z, B2:Z2 and Delivered!$B$2:$Z ...TEXTJOIN can work with maximum of 50000 characters – user0 Feb 20 at 19:00
  • Is the formula searching for a specific area or the recipient ID in Delivered! ? Because I mostly have each half in a significantly different row number – Marek Marhoul Feb 20 at 19:08
  • no, the formula does not search for anything. it creates two virtual columns (one from Ordered sheet an one from Delivered sheet - while from Delivered sheet it joins all items which are under the same ID) with each item per every row and then it filters out stuff between those two VR columns and joins back the result into a single cell. – user0 Feb 20 at 19:27
  • based on similar logic: stackoverflow.com/a/54795616/5632629 – user0 Feb 20 at 21:39

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.