I have been building word lists to use for "smart" color coding. My previous word list formula works well (Doesn't match below table) "SUMPRODUCT(--(NOT(ISERR(SEARCH($J$4:$J$120,'Work Orders'!$E1)))))>0".
I tried adding concatenate function into my formula "SUMPRODUCT(--(NOT(ISERR(SEARCH(****CONCATINATE(------LIST1------$J$4:$J$120------LIST2****..........." but I am getting bad results. I am new to excel and no other coding experience. Is there a formula to search string matches using string concatenation?
Below is an example and I have color coded the answers that I want to return true. See Column H for formulas. H2 is: "=AND(NOT(ISBLANK($C2)),SUMPRODUCT(--(NOT(ISERR(SEARCH(CONCATENATE($F$2:$F$7,$C2,$G$2:$G$6),$D2)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($J$2:$J$5,$D2)))))=0)"
I added spaces to the words to provide proper spacing with concatenation.
In case I am being confusing. Goal is:
Search $D for string match from "any string in $F2:$F7, concated with $C2, concated with any string in $G2:$G6.
I tried adding concatenate function into my formula "SUMPRODUCT(--(NOT(ISERR(SEARCH(****CONCATINATE(------LIST1------$J$4:$J$120------LIST2****..........." but I am getting bad results. I am new to excel and no other coding experience. Is there a formula to search string matches using string concatenation?
Below is an example and I have color coded the answers that I want to return true. See Column H for formulas. H2 is: "=AND(NOT(ISBLANK($C2)),SUMPRODUCT(--(NOT(ISERR(SEARCH(CONCATENATE($F$2:$F$7,$C2,$G$2:$G$6),$D2)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($J$2:$J$5,$D2)))))=0)"
I added spaces to the words to provide proper spacing with concatenation.
Help Needed - Sumproduct search multiple concatnated strings in array.xlsx | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
B | C | D | E | F | G | H | I | J | |||
1 | User Data Sample | Actual Customer Name between these data1 | Actual Customer Name between these data2 | Formula | And does not contain | ||||||
2 | WO | CUSTOMER | PARTS STATUS / JOB COMPLETE | from | inventory | FALSE | PARTS STATUS / JOB COMPLETE | ||||
3 | 101842 | RandCO | From RandCo inventory | Parts are at | stock | TRUE | RM/WH | ||||
4 | 101843 | RandCO | N/A | Parts at | WH | TRUE | 02/WH | ||||
5 | 101844 | RandCO | RandoCo has parts GB/WH | Use | Warehouse | FALSE | GB/WH | ||||
6 | 101845 | Acompany | Parts are in Acompany Warehouse | Using | Parts | TRUE | |||||
7 | 101846 | RandCO | Left Warehouse 02/27/2020 | Parts are in | TRUE | ||||||
8 | 101847 | RandCO | Completed 04/22/2020 | ^^ Note - I want Sumproduct to search $D for matched string from Concat F2:F7&C2&G2:G6^^===>> | TRUE | ||||||
Sumproduct Search |
Cell Formulas | ||
---|---|---|
Range | Formula | |
H2:H8 | H2 | =AND(NOT(ISBLANK($C2)),SUMPRODUCT(--(NOT(ISERR(SEARCH(CONCATENATE($F$2:$F$7,$C2,$G$2:$G$6),$D2)))))>0,SUMPRODUCT(--(NOT(ISERR(SEARCH($J$2:$J$5,$D2)))))=0) |
In case I am being confusing. Goal is:
Search $D for string match from "any string in $F2:$F7, concated with $C2, concated with any string in $G2:$G6.