Excel

Options
Verb
Verb Posts: 227 Forumite
First Post First Anniversary Combo Breaker
in Techie Stuff
Hi
Can someone offer some assistance. I'll try and explain best I can.


I'm trying to add a function to my sheet where I put a value in a specific cell and sheet searches the rest of the sheet and highlights the two in question. I've used conditional formatting but it highlighting all duplicates.


I would like to input a value in cell A1 and on the sheet it highlights A1 and any values that match A1 only


Thanks
«1

Comments

  • Le_Kirk
    Le_Kirk Posts: 22,321 Forumite
    First Anniversary First Post Photogenic Name Dropper
    Options
    Apart from writing a Macro (visual basic) you could use Find & Replace but it is a very manual action.
  • Andy_L
    Andy_L Posts: 12,796 Forumite
    Name Dropper First Post First Anniversary
    Options
    You could use conditional formatting rather than a function
  • DoaM
    DoaM Posts: 11,863 Forumite
    First Post First Anniversary Name Dropper Photogenic
    Options
    Verb wrote: »
    I've used conditional formatting but it highlighting all duplicates

    What's the difference between a duplicate and a matching value?
  • ThemeOne
    ThemeOne Posts: 1,471 Forumite
    First Post Combo Breaker First Anniversary
    Options
    As I understand it, if you type 25 into cell A1 you would like this cell plus any other cells on the spreadsheet which also equal 25 to be highlighted.

    Surely that is the same as highlighting duplicates?

    If this is what you need, then conditional formatting is the way to go.
  • Verb
    Verb Posts: 227 Forumite
    First Post First Anniversary Combo Breaker
    Options
    ThemeOne wrote: »
    As I understand it, if you type 25 into cell A1 you would like this cell plus any other cells on the spreadsheet which also equal 25 to be highlighted.

    Surely that is the same as highlighting duplicates?


    That's what I originally though. But in actual fact anything with a duplicate value is highlighted which means the value I put in A1 is mixed in with the other highlighted values. I would like to just have A1 and any values that match only that
  • Verb
    Verb Posts: 227 Forumite
    First Post First Anniversary Combo Breaker
    Options
    DoaM wrote: »
    What's the difference between a duplicate and a matching value?


    There could be loads of duplicates and only 1 matching value
  • DoaM
    DoaM Posts: 11,863 Forumite
    First Post First Anniversary Name Dropper Photogenic
    Options
    Verb wrote: »
    There could be loads of duplicates and only 1 matching value

    Eh?

    I'll rephrase ... what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?

    (Yes - I've read post #7 ... I'm still none-the-wiser).
  • rmg1
    rmg1 Posts: 3,131 Forumite
    Name Dropper First Anniversary First Post
    Options
    If you don't want A1 to be highlighted, then just exclude it from the conditional formatting.
    Or am I also misreading the question?
    :wall: Flagellation, necrophilia and bestiality - Am I flogging a dead horse? :wall:

    Any posts are my opinion and only that. Please read at your own risk.
  • Cornucopia
    Cornucopia Posts: 16,157 Forumite
    First Anniversary Name Dropper First Post Photogenic
    edited 7 July 2017 at 3:43PM
    Options
    DoaM wrote: »
    Eh?

    I'll rephrase ... what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?

    (Yes - I've read post #7 ... I'm still none-the-wiser).


    Maybe the OP is actually wanting to test 2 values, hence "two" in the original question?

    If so, it'll probably need a formula within the data to combine the values into a single, searchable column and then apply the lookups, conditional formatting, etc. to it.

    Or perhaps, they want to highlight only the first (2?) matching value in the column?

    Basic Conditional formatting certainly works for me (I needed to put the reference cell as fixed location e.g. =$B$4). Once the rule is defined and working for one cell, use the Format Painter tool to apply it to all the others.

    I see that my version of Excel Starter has loads of other goodies under the Conditional Formatting menu. I shall have to explore. :)


    EDIT: Reading it again, I wonder if it is this: Highlight any cells in the data that match A1, AND highlight A1 if any cells match?
  • Chino
    Chino Posts: 2,029 Forumite
    First Anniversary Name Dropper First Post
    Options
    DoaM wrote: »
    what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?
    The conditional formatting option provided by Excel to identify duplicates highlights any cell that has the same value as another cell.
    The OP only wants to highlight cells that have the same value as cell A1.
This discussion has been closed.
★ ★ ★ Meet your Ambassadors

Categories

  • All Categories
  • 343.2K Banking & Borrowing
  • 250.1K Reduce Debt & Boost Income
  • 449.7K Spending & Discounts
  • 235.3K Work, Benefits & Business
  • 608.1K Mortgages, Homes & Bills
  • 173.1K Life & Family
  • 248K Travel & Transport
  • 1.5M Hobbies & Leisure
  • 15.9K Discuss & Feedback
  • 15.1K Coronavirus Support Boards