| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Filtering based on Value
I would like to combine these 2 into one formula
=IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SU M(COUNTIF(C20368, {"DST","LVN","DNM"}))0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) ),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND (LEFT (B20368,1), "JCRAWFUY"))) won't work. The 1st 'True' test looks at Column B and looks for values starting with the Letter J, C, or R while at the same time checking for values in Column C that are either "DST","LVN","DNM" The Second Test looks at Column B and looks for values starting with the Letter A, W, F, U, Y. The test would also be True if Column G contains "SHORT" or "SHT". Finally check for values in Column C that are either "BOG","BLM","CMO" |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filtering based on Value
Perhaps combine the two truth statements with a master OR:
=IF(OR(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF (SUM(COUNTIF(C20368,{"DST","LVN","DNM"}))0,TRUE() ,FALSE())), OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) )),I20368-0.01, J20368) HTH, Bernie MS Excel MVP "caveman.savant" wrote in message ... I would like to combine these 2 into one formula =IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SU M(COUNTIF(C20368, {"DST","LVN","DNM"}))0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) ),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND (LEFT (B20368,1), "JCRAWFUY"))) won't work. The 1st 'True' test looks at Column B and looks for values starting with the Letter J, C, or R while at the same time checking for values in Column C that are either "DST","LVN","DNM" The Second Test looks at Column B and looks for values starting with the Letter A, W, F, U, Y. The test would also be True if Column G contains "SHORT" or "SHT". Finally check for values in Column C that are either "BOG","BLM","CMO" |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filtering based on Value
That's Perfect!
Could the statement OR(ISNUMBER(FIND("SHORT",G20368)),ISNUMBER(FIND("S HT",G20368))) Be simplified to "SHT" since "SHORT" contains those characters? On Apr 27, 9:28*am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Perhaps combine the two truth statements with a master OR: =IF(OR(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF (SUM(COUNTIF(C20368,{"DST ","LVN","DNM"}))0,TRUE(),FALSE())), OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", *G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"}) ))),I20368-0.01, J20368) HTH, Bernie MS Excel MVP "caveman.savant" wrote in message ... I would like to combine these 2 into one formula =IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SU M(COUNTIF(C20368, {"DST","LVN","DNM"}))0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) ),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND (LEFT (B20368,1), "JCRAWFUY"))) won't work. The 1st 'True' test looks at Column B and looks for values starting with the Letter J, C, or R while at the same time checking for values in Column C that are either * "DST","LVN","DNM" The Second Test looks at Column B and looks for values starting with the Letter A, W, F, U, Y. The test would also be True if Column G contains "SHORT" or "SHT". Finally check for values in Column C that are either *"BOG","BLM","CMO" |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filtering based on Value
Nope.
Each is looking for those characters in that sequence. If you only cared about the letters SHT (in that order), you could use an expression like: =(COUNTIF(A1,"*s*h*t*")0) This is not case sensitive, either. "caveman.savant" wrote: That's Perfect! Could the statement OR(ISNUMBER(FIND("SHORT",G20368)),ISNUMBER(FIND("S HT",G20368))) Be simplified to "SHT" since "SHORT" contains those characters? On Apr 27, 9:28 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Perhaps combine the two truth statements with a master OR: =IF(OR(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF (SUM(COUNTIF(C20368,{"DST ","LVN","DNM"}))0,TRUE(),FALSE())), OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) )),I20368-0.01, J20368) HTH, Bernie MS Excel MVP "caveman.savant" wrote in message ... I would like to combine these 2 into one formula =IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SU M(COUNTIF(C20368, {"DST","LVN","DNM"}))0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) ),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND (LEFT (B20368,1), "JCRAWFUY"))) won't work. The 1st 'True' test looks at Column B and looks for values starting with the Letter J, C, or R while at the same time checking for values in Column C that are either "DST","LVN","DNM" The Second Test looks at Column B and looks for values starting with the Letter A, W, F, U, Y. The test would also be True if Column G contains "SHORT" or "SHT". Finally check for values in Column C that are either "BOG","BLM","CMO" -- Dave Peterson |
|
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filtering based on Value
I got it
ISNUMBER(SEARCH("SH*T",G20368)) |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filtering based on Value
You are right. Looking for "SHORT" and "SHT" works. But I want the
leave out "SHIRT". The "SH" order is right and the ending "T", but nothing in the middle. Actually if "B" is the 1st character in column B and column G has "SHORT" or "SHT" I'd find what I'm looking for. On Apr 28, 7:31*am, Dave Peterson wrote: Nope. * Each is looking for those characters in that sequence. If you only cared about the letters SHT (in that order), you could use an expression like: =(COUNTIF(A1,"*s*h*t*")0) This is not case sensitive, either. "caveman.savant" wrote: That's Perfect! Could the statement OR(ISNUMBER(FIND("SHORT",G20368)),ISNUMBER(FIND("S HT",G20368))) Be simplified to "SHT" since "SHORT" contains those characters? On Apr 27, 9:28 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Perhaps combine the two truth statements with a master OR: =IF(OR(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF (SUM(COUNTIF(C20368,{"DST ","LVN","DNM"}))0,TRUE(),FALSE())), OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", *G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"}) ))),I20368-0.01, J20368) HTH, Bernie MS Excel MVP "caveman.savant" wrote in message .... I would like to combine these 2 into one formula =IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SU M(COUNTIF(C20368, {"DST","LVN","DNM"}))0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) ),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND (LEFT (B20368,1), "JCRAWFUY"))) won't work. The 1st 'True' test looks at Column B and looks for values starting with the Letter J, C, or R while at the same time checking for values in Column C that are either * "DST","LVN","DNM" The Second Test looks at Column B and looks for values starting with the Letter A, W, F, U, Y. The test would also be True if Column G contains "SHORT" or "SHT". Finally check for values in Column C that are either *"BOG","BLM","CMO" -- Dave Peterson |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Filtering based on Value
I don't understand.
SHORT is ok, but you don't want anything in the middle. What about that OR in the middle of SHORT? If I were only looking for SHT or SHORT, I'd use the suggesting that Bernie gave--it only looks for those two choices. If you wanted to add a check that b20368 started with a B: =AND(LEFT(B20368,1)="B", OR(ISNUMBER(FIND("SHORT",G20368)),ISNUMBER(FIND("S HT",G20368)))) "caveman.savant" wrote: You are right. Looking for "SHORT" and "SHT" works. But I want the leave out "SHIRT". The "SH" order is right and the ending "T", but nothing in the middle. Actually if "B" is the 1st character in column B and column G has "SHORT" or "SHT" I'd find what I'm looking for. On Apr 28, 7:31 am, Dave Peterson wrote: Nope. Each is looking for those characters in that sequence. If you only cared about the letters SHT (in that order), you could use an expression like: =(COUNTIF(A1,"*s*h*t*")0) This is not case sensitive, either. "caveman.savant" wrote: That's Perfect! Could the statement OR(ISNUMBER(FIND("SHORT",G20368)),ISNUMBER(FIND("S HT",G20368))) Be simplified to "SHT" since "SHORT" contains those characters? On Apr 27, 9:28 am, "Bernie Deitrick" <deitbe @ consumer dot org wrote: Perhaps combine the two truth statements with a master OR: =IF(OR(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF (SUM(COUNTIF(C20368,{"DST ","LVN","DNM"}))0,TRUE(),FALSE())), OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) )),I20368-0.01, J20368) HTH, Bernie MS Excel MVP "caveman.savant" wrote in message ... I would like to combine these 2 into one formula =IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SU M(COUNTIF(C20368, {"DST","LVN","DNM"}))0,TRUE(),FALSE())),I20368-0.01,J20368) =IF(OR(AND(B20368<"", ISNUMBER(FIND(LEFT(B20368,1), "AWFUY"))),OR(ISNUMBER(FIND("SHORT", G20368)),ISNUMBER(FIND("SHT", G20368))),SUM(COUNTIF(C20368,{"BOG","BLM","CMO"})) ),I20368-0.01, J20368) Each statement stands alone as far as filtering, so ISNUMBER(FIND (LEFT (B20368,1), "JCRAWFUY"))) won't work. The 1st 'True' test looks at Column B and looks for values starting with the Letter J, C, or R while at the same time checking for values in Column C that are either "DST","LVN","DNM" The Second Test looks at Column B and looks for values starting with the Letter A, W, F, U, Y. The test would also be True if Column G contains "SHORT" or "SHT". Finally check for values in Column C that are either "BOG","BLM","CMO" -- Dave Peterson -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Create a worksheet based on filtering of another? | Excel Discussion (Misc queries) | |||
| Filtering data based on formula | Excel Discussion (Misc queries) | |||
| Filtering Based on External Criteria | Excel Discussion (Misc queries) | |||
| filtering based on a value in a cell | Excel Worksheet Functions | |||
| advanced filtering based on entries ABOVE those I wish to keep | Excel Discussion (Misc queries) | |||
Linear Mode
