Announcement

Collapse
No announcement yet.

Search Multiple Ranges for Keywords within Text Strings

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Search Multiple Ranges for Keywords within Text Strings



    I Need to search multiple ranges for keywords & phrases within text strings. These text strings are basically paragraphs of user notes. Now I had a formula that worked perfectly (and I was so proud of myself) until I was advised by my boss that we could not include Column Q within the search criteria. The old and new ranges along with the criteria I am using is listed below, along with my functional formula. I would think I could simply add an AND/OR and list the formula twice with each of my new ranges, however, if that is what I need to do, I am having difficulty making that function properly.

    OLD Range $A2:$AG2
    NEW Range 1 $A2:$P2
    NEW Range 2 $R2:$AI2
    Range for keyword list error
    Display if TRUE Human Error

    =IF(SUMPRODUCT(--ISNUMBER(SEARCH(error,$A2:$AG2))),"Human Error","")
    Sherry
    Orlando, FL

  • #2
    Hi

    Can you post a sample workbook pls,

    With an expected result will be more helpful.

    Regards

    Maqbool

    Comment


    • #3
      Please Note that I provided 2 examples in my log. The current range as I mentioned needs to become slightly larger and also most importantly SKIP column Q. Column Q data is generated from a pull down menu consisting of only about 8 choices, and people often choose incorrectly. I need my analysis of this data to be driven by the search parameters.

      Row 2 data pulls correctly. "Human Error" is listed in column R, and displays in column AM
      Row 3 data pulls incorrectly. "Human error" is listed in Column Q (the drop down from the source), but it displays in column AM although those keywords nor nothing else from the keyword list actually appear in this row. If you change the data in column Q you will notice that column AM no longer populates.

      I need to make sure column Q is excluded from my search range. Thanks!

      Attached Files
      Sherry
      Orlando, FL

      Comment


      • #4
        try this formula

        Try this formula. Column letter & Row number are for example only. Array formulas will have curly brackets {} emcompassing the ENTIRE formula. You do not add these, you Enter the formula with Ctrl+Shift+Enter and Excel will add them (details: Array formulas). Many more Excel Formulas
         Z
        100 
        IF(OR(SUMPRODUCT(--ISNUMBER(SEARCH(error,$A2:$P2))),SUMPRODUCT(--ISNUMBER(SEARCH(error,$R2:$AF2)))),"Human Error","")
         



        Regards

        Maqbool

        Comment


        • #5


          Maqbool,

          Works perfectly! I knew it could be done, and although I was close in my attempts I simply did not have the correct "formula grammar" to make it work. Thanks so much for all your help, it is greatly appreciated! I am going to save this formula in my notes.

          Thanks,
          Webbers
          Sherry
          Orlando, FL

          Comment

          Working...
          X