Filtering based on Value

  • Thread starter Thread starter caveman.savant
  • Start date Start date
C

caveman.savant

I would like to combine these 2 into one formula

=IF(AND(ISNUMBER(FIND(LEFT(B20368,1),"JCR")),IF(SUM(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"
 
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
 
That's Perfect!

Could the statement
OR(ISNUMBER(FIND("SHORT",G20368)),ISNUMBER(FIND("SHT",G20368)))

Be simplified to "SHT" since "SHORT" contains those characters?
 
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.
 
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.
 
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("SHT",G20368))))
 
Back
Top