Google-sheets – Processing data from multiple cells and comparing results with another cell

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryvlookup

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

Best Answer

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"))

Best Answer

EU syntax:

US syntax:

Related Solutions

Google-sheets – Query Multiple Ranges with Results in Row

Google-sheets – Copying data from one cell in another based on date

Related Topic