|
|
| ||||
|
| 501 |
So column A is a shift pattern; column B is a lookup value based on binary; column C is the corresponding code relating to days of the week worked. e.g. M,T,W,Th,F all worked = 501
However, there are instances as above whereby the data in-putter includes a '0' for a particular day - my question is, is it possible to include a formula that detects the (M etc.)=0, doesn't include it in the binary, and brings back the correct schedule code?
Row 1 should therefore read 0011110 and 405. Formula to detect binary - =TEXTJOIN("",,ISNUMBER(SEARCH("(* "&{"Sn","M","T","W","Th","F","S"}&"=* *)",SUBSTITUTE(SUBSTITUTE(F1274,"(","( "),")"," )")))+0)
Formula to detect code - =IF(ISNUMBER(SEARCH("37*",F1274)),$B$42,INDEX($B$2:$B$43,MATCH(G1274,$C$2:$C$43,0)))
Row 2 should read 0111100 and 401. Binary - =TEXTJOIN("",,ISNUMBER(SEARCH("(* "&{"Sn","M","T","W","Th","F","S"}&"=* *)",SUBSTITUTE(SUBSTITUTE(F1281,"(","( "),")"," )")))+0)
Code - =IF(ISNUMBER(SEARCH("37*",F1281)),$B$42,INDEX($B$2:$B$43,MATCH(G1281,$C$2:$C$43,0)))
Thanks in advance for any help!!