Results 1 to 4 of 4

Thread: Conditional formatting with wildcard match

  1. #1

    Conditional formatting with wildcard match



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I am trying to generate a cross reference highlight format.

    I am using this Conditional Format Formula to check and highlight values list in B2:K100 not found in Column M.

    B2:K100 Has values something like this

    10001 10005
    10002 10006

    Column M has data inserted from another program and looks like this

    9JFK20002 1" 6C LMNOP RIP 10002 644.000


    I am using this Conditional Format Formula to check and highlight values list in B2:K100 not found in Column M.

    =ISNA(MATCH("*"&B2&"*",$M$1:$M$500,0))

    This works wonderfully. What I need now is a cross reference in Column M. I need to look in column M specifically for the 10002 value in B2:K100. If no match is found, then highlight.

    I would assume all I need to do is switch the "*" wildcard function up a bit, but I am not sure exactly how.
    Attached Files Attached Files

  2. #2
    Magician navic's Avatar
    Join Date
    Aug 2013
    Location
    Europe, Croatia
    Posts
    869
    Articles
    0
    Excel Version
    Excel 2013
    Quote Originally Posted by cwhite86 View Post
    What I need now is a cross reference in Column M. I need to look in column M specifically for the 10002 value in B2:K100. If no match is found, then highlight.
    If I understand you well you want to highlight a cell in column M that contain a specific number as a condition.
    Try this formula applied in the Conditional Formatting on the range in column M. Select range M5 to last row data
    Code:
    =IF(IFERROR(VALUE(LEFT(RIGHT($M5,LEN($M5)-FIND(TEXT($U$1,0),$M5)+1),5))=$U$1,"")=TRUE,TRUE,FALSE)
    In cell U1 is a condition
    See Attachment
    Attached Files Attached Files
    My personal Web Excel Tutorials
    Attention! In the formulas as a delimiter I use a semicolon (; ) if you using my formula, then perhaps you need to use a comma (,)
    Also for a decimal number I use a comma (,) you may need to use point (.) instead of

  3. #3
    Hi, I read your question a little differently to navic. navic's formula is perfect if you do want to just match that one number, but I got the feeling that you wanted each cell in column M to check if it has a matching number in B2:K100.
    If that's true, then I think this will work (although I don't know what will happen with different dollar amounts).
    Code:
    =AND(COUNTIF($B$2:$K$100,LEFT(RIGHT(M1,21),5))=0,ISNUMBER(VALUE(LEFT(RIGHT(M1,21),5))),LEN(M1)>20)
    Attached Files Attached Files

  4. #4
    Thanks for your help both of you. This is what I ended up with.


    =NOT(SUMPRODUCT(COUNTIF(A1,"*"&BI2C!$B$2:$K$100&"*"), --(BI2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&BW1C!$B$2:$K$100&"*"), --(BW1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&BW2C!$B$2:$K$100&"*"), --(BW2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&CHY1C!$B$2:$K$100&"*"), --(CHY1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&CHY2C!$B$2:$K$100&"*"), --(CHY2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HK1C!$B$2:$K$100&"*"), --(HK1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HK2C!$B$2:$K$100&"*"), --(HK2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HKBWP!$B$2:$K$100&"*"), --(HKBWP!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HM1C!$B$2:$K$100&"*"), --(HM1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&HM2C!$B$2:$K$100&"*"), --(HM2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RO1C!$B$2:$K$100&"*"), --(RO1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RO2C!$B$2:$K$100&"*"), --(RO2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RQWO1C!$B$2:$K$100&"*"), --(RQWO1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&RQWO2C!$B$2:$K$100&"*"), --(RQWO2C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&WO1C!$B$2:$K$100&"*"), --(WO1C!$B$2:$K$100<>"")) +SUMPRODUCT(COUNTIF(A1,"*"&WO2C!$B$2:$K$100&"*"), --(WO2C!$B$2:$K$100<>"")))*(A1<>"")
    This is because I needed to search multiple columns on multiple pages also. While lengthy this did the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •