+ Reply to Thread
Results 1 to 6 of 6

Find and highlight (using formula) names from a cell that contains the full name

  1. #1
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Exclamation Find and highlight (using formula) names from a cell that contains the full name

    Yes, the title is a bit confusing but if someone has a better way of explaining it (after reading my post) please do let me know, I would gladly replace it! :-)

    Anyways, here we go.

    I have one list of names. These are full names. For example, a name in the list would be:

    Junior John Jeremy Jackson Juvenile.

    Then, I have another list of names but they are partial names but belong to the above mentioned list. For example, a name in the list would be:

    Junior Juvenile.

    It is to be noted that both names are actually belonging to one person but one name is short and the other full.

    I want to use Junior Juvenile in order to highlight Junior John Jeremy Jackson Juvenile.

    I think there would be a way do it using Conditional formatting but a formula would be also much appreciated.

    I really hope I explained it well.

    Thank you very much guys in advance!

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Find and highlight (using formula) names from a cell that contains the full name

    This is probably more comlicated than it sounds. So...

    A picture is worth 1,000 words. An Excel sheet is worth 1,000 non-editable pictures.

    Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet complete with an explanation, REPRSENTATIVE data and some expected results.
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  3. #3
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: Find and highlight (using formula) names from a cell that contains the full name

    No, it is not confusing or difficult.

    I am making it confusing and difficult.

    As you can see in the attachment.

    There are short names and then full.

    They are also mixed in together (up and down).

    We just have to find which belong which and eventually highlight.

    Thank you very much for your prompt responsiveness!
    Attached Files Attached Files

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Find and highlight (using formula) names from a cell that contains the full name

    Hahaha. I'll reserve judgement about that for a moment. However, this works for your sample....

    =IFERROR(INDEX(A:A,MATCH(SUBSTITUTE(B1," ","*"),A:A,0)),"")
    Attached Files Attached Files

  5. #5
    Forum Contributor
    Join Date
    03-26-2021
    Location
    California, US
    MS-Off Ver
    MS Office 2010
    Posts
    105

    Re: Find and highlight (using formula) names from a cell that contains the full name

    Wow, man, amazing!

    What a life saver!

    See, you did in a jiffy! :-D

    But here is another concern.

    How would I simply highlight column A by using the names in column B?

    Basically, I am kind of suggesting and requesting whether there is a way of using Conditional Formatting by simply selecting both columns in order to result in highlighting those that belong to each other.

    The formula is impressive but I will be never able to learn it. :-(

  6. #6
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    43,986

    Re: Find and highlight (using formula) names from a cell that contains the full name

    Yes. You can use this as the conditional formatting formula:

    =SUM(IFERROR(MATCH(SUBSTITUTE($B$1:$B$16," ","*"),A1,0),""))>0

    here applied down to row 40.

    You're welcome.



    It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

    Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.
    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. Replies: 5
    Last Post: 03-05-2020, 06:34 PM
  2. separating full names with a formula
    By lacombeg in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 02-24-2020, 11:35 AM
  3. Replies: 4
    Last Post: 06-04-2019, 01:09 PM
  4. Find Duplication names and highlight missing - macro request
    By missy22 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 06-20-2014, 09:09 AM
  5. Find similar names and highlight
    By rizmomin in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 12-07-2013, 10:56 AM
  6. [SOLVED] Formula to Find, Match & Highlight a duplicate cell in C.F.
    By alan_stephen75@ in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 04-11-2013, 07:09 AM
  7. [SOLVED] how do I find names in a workbook full of names
    By aj in forum Excel General
    Replies: 1
    Last Post: 01-19-2006, 05:10 PM

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