+ Reply to Thread
Results 1 to 16 of 16

Formula to Find, Match & Highlight a duplicate cell in C.F.

  1. #1
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Formula to Find, Match & Highlight a duplicate cell in C.F.

    I am trying to create a formula so that if the “Text”(name) in cell G49, which will change now and then, finds one or more “Matches” in the range E56:P60 the text of these duplicate names in the cells will turn Bold & Red but if G49 is blank nothing changes.

    I am familiar with C.F. and I have tried "Match(G49)=" and Countif but these didn’t seem to work. Can anyone help please?

    Thanks for looking.

  2. #2
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    in e56 cf just needs to be =$g$49 then copy the format to the whole range e56:p60
    "Unless otherwise stated all my comments are directed at OP"

    Mojito connoisseur and now happily retired
    where does code go ?
    look here
    how to insert code

    how to enter array formula

    why use -- in sumproduct
    recommended reading
    wiki Mojito

    how to say no convincingly

    most important thing you need
    Martin Wilson: SPV
    and RSMBC

  3. #3
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Hi
    select range E56:P60 and use =COUNTIF($E$56:$P$60,G49) as CF and format as needed

    EDIT mdw 's is much easier

  4. #4
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Many thanks to both of you for your speed of response.

  5. #5
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Sorry Guys, in CF I used “ > Use a formula to determine which cells to format” and tried both of your formulas and it only partly worked.

    What seems to happen is that the first cell only in the range E56:P60 (E56) is highlighted irrespective of the name that’s in G49 in other words if I put in SCO in G49 even although cell E56 (the first cell in the range) contains CAN and is not a match that cell only is highlighted, when blank all is well.

    Anything to do with the fact that the names in the range are input via a formula and not directly?

    I am sure it’s me. Regards

  6. #6
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    well it should work post a sample workbook that doesnt

  7. #7
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    It's a mess but is only a test at the moment. Thanks
    Attached Files Attached Files

  8. #8
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    well cf should be either (i made a mistake in the earlier post)
    formula is =E56=$G$49
    in e56 copied across range or
    use the alternative
    cell value is equal to =$G$49

  9. #9
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    That's great, works a treat now, once again many thanks.

  10. #10
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Oh it's me again, sorry just noticed if G49 is blank and if I use Fill as well as Red text then all empty cells within the range are filled with colour. Is there any way that the CF/Formula can leave empty cells with no colour? Sorry to be a pest.

  11. #11
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Add another CF rule for 'blanks'

    Rank this rule higher and check the 'Stop if true' checkbox

    See attached
    Attached Files Attached Files
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  12. #12
    Forum Expert martindwilson's Avatar
    Join Date
    06-23-2007
    Location
    London,England
    MS-Off Ver
    office 97 ,2007
    Posts
    19,320

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    formula is =AND($G$49<>"",E56=$G$49)

  13. #13
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Terrific, many thanks

  14. #14
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    I am now wondering if the formula can be amended and what it would be so that even if the matching name is not always exactly the same but could have an asterisk in front or behind so that in one cell it could be say CAN and in the next cell *CAN or even CAN* but these 3 variations would still count as a “match”.

    I hope you can follow what I am looking for and help. Regards

  15. #15
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Try

    =AND($G$49<>"",E56<>"",MATCH("*"&E56&"*",$G$49,0))

  16. #16
    Registered User
    Join Date
    04-06-2007
    Location
    Glasgow, Scotland
    MS-Off Ver
    2003 / 2007
    Posts
    83

    Re: Formula to Find, Match & Highlight a duplicate cell in C.F.

    Many thanks for your excellent response

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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