If you are willing to add a helper column to your sab_prod table, here is something to consider. The helper column is populated with this formula, pulled all the way down:
Excel Formula:
=IF(Table1[@item]=MAX(FILTER(Table1[item],(Table1[job number]=Table1[@[job number]])*(INT(Table1[item])=INT(Table1[@item])))),1,0)
This determines whether the line contains the largest "item" value among any other lines that share the same combination of job number and integer value of the item number. I put this helper column in column Y as shown in this small snippet from the sab_prod sheet:
| forum_ex_v1.xlsx |
|---|
|
|---|
| P | Q | R | S | T | U | V | W | X | Y |
|---|
| 1 | mch-rtr | ship-act | cancel | mch-cmpl | epl-stock | rtr-stock | misc-stock | edate | | maxitm |
|---|
| 2 | MD20,CS,OE,8VN,FR8,CG24,C2 | 4/7/2022 | FALSE | ### | ### | ### | ### | April 2022 | | 1 |
|---|
| 3 | RETURN=WR | | FALSE | ### | ### | ### | ### | June 2022 | | 0 |
|---|
| 4 | MD20,CS,OE,8VN,FR8,C1 | 4/12/2022 | FALSE | ### | ### | ### | ### | April 2022 | | 1 |
|---|
|
|---|
Then on the Airlocks sheet, the following LET function is used for a few reasons to compute the sum of quantity values (see column K formulas):
1. It allows you to create various search groups (arrays) of materials, since it appears that you tailor the formula to look for multiple materials. So material group 1 (matg1) can be defined as shown, and so on for other material groups. Then to establish which material group to search for, the "mats, matg1" line is used (in this case, to assign the matg1 material group to the mats variable name). This offers some flexibility in being able to quickly edit the formula to invoke the desired material group.
2. A similar approach could be used to add multiple description search terms. In the posted example, only one "MD20 air" is present, but you could establish various text strings for desc1, desc2, etc., and then assign one of them to the desc variable name in the formula.
3. The date variable name is an array that invokes the rule you described to use column J (job-pln) for entries having a dot, and column Q (ship-act) for entries without a dot. To avoid hardwiring the sheet name and ranges into this formula in many places, I've assigned the entire relevant portion of the sap_prod table to a variable called tblprod, and then all references to columns in tblprod use an INDEX(tblprod,,column index number) construction. So columns J and Q and column indexes 10 and 17 in the formula. Other than the first two lines that tailor the search criteria, the rest of the formula will most likely not require further editing.
4. The various match criteria are used in a FILTER function to aggregate only those lines that satisfy the description text string, include any of the material types specified, whose "date" is within limits specified, whose cancel status is FALSE, and whose item value is the maximum for that particular combination of job number and item number integer value. This FILTER function returns columns A:R of tblprod in case further inspection of the results is necessary. To trim these results down further, the FILTER function is wrapped inside another FILTER function, which now allows for the selection of specific columns for reporting back. In this case, I've specified columns 1,3,5,6,7,10,17. The quantity sum that is desired would rely on values in the 4th column of this secondarily filtered array (or column 6 of the original tblprod). So the last statement in the LET function indicates to return the sum of the quantity values.
If you want to see the items that are used for the sum, the same LET function is used, except the SUM at the end is replaced to report back only the results table (named res), as shown in cell N3 for the row 3 details that yielded a quantity of 27. These results will "spill" so be certain to have sufficient room to accommodate them. After inspecting, just delete the N3 formula.
| forum_ex_v1.xlsx |
|---|
|
|---|
| G | H | I | J | K | L | M | N | O | P | Q | R | S | T |
|---|
| 1 | Number w/0 dotline | Number with Dotline | Time Saved (W/o) | Time Save With | Sum of Qty | | | | | | | | | |
|---|
| 2 | 9 | 18 | #REF! | 108 | 9 | Date Range | | | | | | | | |
|---|
| 3 | 23 | 47 | 69 | 141 | 27 | 1/1/2022 | | 1100450133 | 250 | DM STEEL | 2 | MD20 AIRLOCK | 44652 | 44658 |
|---|
| 4 | | | | | | 12/31/2022 | | 1100458921 | 20 | RALCORP FROZEN BAKERY | 2 | MD20 AIRLOCK | 44652 | 44663 |
|---|
| 5 | | | | | | | | 1100102103 | 2801 | NESTLE PURINA PETCARE | 1 | MD20 AIRLOCK | 44659 | 44679 |
|---|
| 6 | | | | | | | | 1100102076 | 2601 | AZEK BUILDING PRODUCTS INC | 1 | MD20 AIRLOCK | 44694 | 44705 |
|---|
| 7 | | | | | | | | 1100465821 | 10 | MOTION INDUSTRIES | 1 | MD20 AIRLOCK MOTION INDUSTRIES | 44701 | 44705 |
|---|
| 8 | | | | | | | | 1100102138 | 1801 | AZEK BUILDING PRODUCTS | 1 | MD20 AIRLOCK | 44683 | 44686 |
|---|
| 9 | | | | | | | | 1100458984 | 190 | BERRY GLOBAL | 2 | MD20 AIRLOCK | 44652 | 44656 |
|---|
| 10 | | | | | | | | 1100467566 | 20 | SOUTHWIRE COMPANY LLC | 1 | MD20 AIRLOCK | 44694 | 44700 |
|---|
| 11 | | | | | | | | 1100467578 | 20 | SOUTHWIRE COMPANY LLC | 1 | MD20 AIRLOCK | 44694 | 44697 |
|---|
| 12 | | | | | | | | 1100467588 | 20 | SOUTHWIRE COMPANY LLC | 1 | MD20 AIRLOCK | 44694 | 44699 |
|---|
| 13 | | | | | | | | 1100467837 | 10 | RAGASA INDUSTRIAS SA DE CV | 1 | MD20 AIRLOCK | 44652 | 44665 |
|---|
| 14 | | | | | | | | 1100468093 | 10.1 | INGEVITY CORPORATION | 1 | WEAR RESISTANT MD20 AIRLOCK INGEVITY - W.R. | 44701 | 44700 |
|---|
| 15 | | | | | | | | 1100468615 | 10 | QUIKRETE COMPANIES | 1 | MD20 AIRLOCK | 44687 | 44686 |
|---|
| 16 | | | | | | | | 1100469485 | 10 | BAGHOUSE & INDUSTRIAL | 4 | MD20 AIRLOCK | 44683 | 44683 |
|---|
| 17 | | | | | | | | 1100455881 | 30.1 | BORAL ROOFING LLC | 1 | WEAR RESISTANT MD20 AIRLO - Wear Resistant | 44652 | 44655 |
|---|
| 18 | | | | | | | | 1100461845 | 22 | NESTLE PURINA PETCARE | 4 | MD20 AIRLOCK | 44687 | 44705 |
|---|
| 19 | | | | | | | | 1100470746 | 10.1 | ANCHOR GLASS CONTAINER CORPORATION | 1 | MD20 AIRLOCK ANCHOR GLASS - NEDOX | 44694 | 44698 |
|---|
| 20 | | | | | | | | 1100475225 | 10.1 | COVANTA ENERGY | 1 | MD20 AIRLOCK - HARD CHROME bore & ep interior | 44743 | 0 |
|---|
|
|---|
