+ Reply to Thread
Results 1 to 9 of 9

Need to find+highlight or return a row/cell based on a text input

  1. #1
    Registered User
    Join Date
    09-15-2020
    Location
    Graz
    MS-Off Ver
    O365; Excel: Version 2007
    Posts
    5

    Need to find+highlight or return a row/cell based on a text input

    Hello There,

    I need to be able to help my fellow colleagues out with this table:
    Our folks want to be able to quickly define what ID number does a given task have, based on a free text entry where the formula is looking for that specific text string in the other columns containing descriptions and further details and such.

    The best way to visualize the resulting ID number of that task would be to either:
    a; list all the task ID numbers where that text string has been found
    b; highlight those cells or rows where that text string has been found



    Example:
    the user inputs into a search dialog box(?): 'jump'; rows ID number 1 and 2 would be highlighted, or at least all cells/words containing 'jump';
    the user inputs into a search dialog box(?): 'under'; row number 3 would be highlighted or at least all cells/words containing 'under'

    the visualization is important since I want to ensure that the user has a freedom of choice which ID numbers to choose; or if it would be possible to use multiple comma separated keywords like: 'jump, fox' then only row ID number 1 would be highlighted since row ID number 2 doesn't contain 'fox' - this would be awesome

    thank you if you can help me out.

    Is it maybe easier to do this in PowerBI I'm wondering right now?
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Need to find+highlight or return a row/cell based on a text input

    would jump and fox have to be in the same cell for the row to be matched?

    a conditional format in b2using a formula of countif(b2,"*"&$h$1&"*") would highlight all the cells containing the word in h1, format as you see fit

    for 2 words in h1 and h2
    countifs(b2,"*"&$h$1&"*",b2,"*"&$h$2&"*")

    for words anwhere in the row, highlighting the whole row
    countif($b2:$f2,"*"&$h$1&"*") *countif($b2:$f2,"*"&$h$2&"*")

  3. #3
    Registered User
    Join Date
    09-15-2020
    Location
    Graz
    MS-Off Ver
    O365; Excel: Version 2007
    Posts
    5

    Re: Need to find+highlight or return a row/cell based on a text input

    Quote Originally Posted by davsth View Post
    would jump and fox have to be in the same cell for the row to be matched?
    : no

    thank you for your suggestion, I'll try to implement it tomorrow - what about the exclusions if only one string of the two strings matches for a row and not both, is this included in this formula?

  4. #4
    Registered User
    Join Date
    09-15-2020
    Location
    Graz
    MS-Off Ver
    O365; Excel: Version 2007
    Posts
    5

    Re: Need to find+highlight or return a row/cell based on a text input

    I tried to implement what you suggested but failed.
    See the attached file.
    I reckon maybe the description of my goals was not well defined, but I need the formula or script to look for the one or two keywords (say in B1 and in B2) at once in all of the rows/table (D4 til H38), and then highlight the row(s) containing one or both keywords (and eventually do the exclusion of a row if not both keywords have been found in that row).
    Attached Files Attached Files

  5. #5
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Need to find+highlight or return a row/cell based on a text input

    you but too many " in the formula to stop it working, see amend conditional format and the formula added to column I, then you can filter by this to exclude rows and avoid vba. currently it does both wif you change the * between the countifs to a + it will change to an or condition instead of an &
    Attached Files Attached Files
    Last edited by davsth; 09-16-2020 at 03:33 AM.

  6. #6
    Registered User
    Join Date
    09-15-2020
    Location
    Graz
    MS-Off Ver
    O365; Excel: Version 2007
    Posts
    5

    Re: Need to find+highlight or return a row/cell based on a text input

    Hi Dave,

    great, that is almost perfect!

    Now, if I enter only one keyword and leave the second empty, then all rows get highlighted - can you amend the formula in a way that

    if the second keyword is not entered

    then it either doesn't get searched

    or let the formula think that the second keyword /that is actually missing/ is equal to the first keyword

  7. #7
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Need to find+highlight or return a row/cell based on a text input

    which formula are you using?


    countif($b2:$f2,"*"&$h$1&"*") + if(h1="",0,countif($b2:$f2,"*"&$h$2&"*") )adjusting the ranges accordingly, I haven't got the sheet open

  8. #8
    Registered User
    Join Date
    09-15-2020
    Location
    Graz
    MS-Off Ver
    O365; Excel: Version 2007
    Posts
    5

    Re: Need to find+highlight or return a row/cell based on a text input

    nah, can't implement that additional "if" for the second countif, it brings me to an error message

    but otherwise I like it that way, I'll check up with my fellow designated user tomorrow if he likes it.

  9. #9
    Forum Expert
    Join Date
    10-10-2016
    Location
    Sheffield
    MS-Off Ver
    365 and rarely 2016
    Posts
    3,198

    Re: Need to find+highlight or return a row/cell based on a text input

    If it is an or
    =COUNTIF($D4:$H4,"*"&$B$1&"*") +IF($B$2<>"",COUNTIF($D4:$H4,"*"&$B$2&"*"))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Highlight Cell value in color based on the input value in a another cell
    By Kamalakar M in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-26-2016, 10:33 AM
  2. VBA code to find the row number based on data via input box and the highlight a cell
    By Aditya Sabat in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-21-2014, 05:47 PM
  3. [SOLVED] Find text and highlight in Excel cell
    By mqcai4613 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-16-2013, 08:41 AM
  4. a macro to find text based on user input
    By J4kub in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-14-2013, 07:46 AM
  5. [SOLVED] VLOOKUP - to return a value based on what I input to a cell?
    By AndyHawke in forum Excel General
    Replies: 14
    Last Post: 09-25-2012, 04:31 AM
  6. a macro to find text based on user input
    By CDPRINT in forum Excel Programming / VBA / Macros
    Replies: 20
    Last Post: 08-29-2010, 12:23 PM
  7. Highlight Part Of Cell Based On Input
    By sysco in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-05-2006, 09:12 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1