API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Feb 20th, 2019
72
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 8.60 KB | None | 0 0
raw download clone embed print report
  1. =IF(ARRAYFORMULA(IFERROR(SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1,
  2. SUBSTITUTE(B2:E2, ", ", ",")), ",")); TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
  3. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  4. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  5. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  6. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
  7. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))},
  8. "limit "&COUNTA(TRANSPOSE(
  9. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  10. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  11. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  12. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0),
  13. NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ","));
  14. TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
  15. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  16. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  17. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  18. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
  19. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))},
  20. "limit "&COUNTA(TRANSPOSE(
  21. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  22. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  23. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  24. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0),
  25. TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  26. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  27. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  28. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))))), "♂", ""), ))="",
  29. "OK", ARRAYFORMULA(textJOIN(", ", 1, SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1,
  30. SUBSTITUTE(B2:E2, ", ", ",")), ",")); TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
  31. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  32. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  33. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  34. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
  35. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))},
  36. "limit "&COUNTA(TRANSPOSE(
  37. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  38. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  39. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  40. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0),
  41. NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ","));
  42. TRANSPOSE(SPLIT(REPT("♂♀", COUNTA(TRANSPOSE(
  43. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  44. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  45. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  46. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1)))-
  47. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(",", 1, SUBSTITUTE(B2:E2, ", ", ",")), ",")))+1), "♀"))},
  48. "limit "&COUNTA(TRANSPOSE(
  49. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  50. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  51. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  52. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))), 0),
  53. TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN("", 1, ","&
  54. {VLOOKUP(A2, Delivered!$A$2:$E, 2, 0), SUBSTITUTE(SPLIT(CONCATENATE(
  55. FILTER(Delivered!$B$2:$E, Delivered!$A$2:$A=A2)&CHAR(9)&";"), ";"), CHAR(9), " ")}&
  56. "×"), " ", ","), ",,", ","), ", ", ","), " ×", ""), "×", ""), ",", 1, 1))))), "♂", ""))))
  57.  
  58. =IF(ARRAYFORMULA(IFERROR(SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1;
  59. SUBSTITUTE(B2:E2; ", "; ",")); ",")); TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
  60. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  61. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  62. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  63. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
  64. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))};
  65. "limit "&COUNTA(TRANSPOSE(
  66. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  67. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  68. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  69. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0);
  70. NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ","));
  71. TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
  72. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  73. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  74. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  75. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
  76. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))};
  77. "limit "&COUNTA(TRANSPOSE(
  78. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  79. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  80. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  81. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0);
  82. TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  83. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  84. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  85. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))))); "♂"; ""); ))="";
  86. "OK"; ARRAYFORMULA(textJOIN(", "; 1; SUBSTITUTE(FILTER(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1;
  87. SUBSTITUTE(B2:E2; ", "; ",")); ",")); TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
  88. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  89. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  90. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  91. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
  92. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))};
  93. "limit "&COUNTA(TRANSPOSE(
  94. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  95. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  96. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  97. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0);
  98. NOT(COUNTIF(QUERY({TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ","));
  99. TRANSPOSE(SPLIT(REPT("♂♀"; COUNTA(TRANSPOSE(
  100. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  101. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  102. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  103. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1)))-
  104. COUNTA(TRANSPOSE(SPLIT(TEXTJOIN(","; 1; SUBSTITUTE(B2:E2; ", "; ",")); ",")))+1); "♀"))};
  105. "limit "&COUNTA(TRANSPOSE(
  106. SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  107. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  108. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  109. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))); 0);
  110. TRANSPOSE(SPLIT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(TEXTJOIN(""; 1; ","&
  111. {VLOOKUP(A2; Delivered!$A$2:$E; 2; 0) SUBSTITUTE(SPLIT(CONCATENATE(
  112. FILTER(Delivered!$B$2:$E; Delivered!$A$2:$A=A2)&CHAR(9)&";"); ";"); CHAR(9); " ")}&
  113. "×"); " "; ","); ",,"; ","); ", "; ","); " ×"; ""); "×"; ""); ","; 1; 1))))); "♂"; ""))))
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!