Your browser isn't supported
It looks like you're using an old web browser. To get the most out of the site and to ensure guides display correctly, we suggest upgrading your browser now. Download the latest:

Welcome to the MSE Forums

We're home to a fantastic community of MoneySavers but anyone can post. Please exercise caution & report spam, illegal, offensive or libellous posts/messages: click "report" or email forumteam@. Skimlinks & other affiliated links are turned on

Search
  • FIRST POST
    • Verb
    • By Verb 7th Jul 17, 9:28 AM
    • 159Posts
    • 5Thanks
    Verb
    Excel
    • #1
    • 7th Jul 17, 9:28 AM
    Excel 7th Jul 17 at 9:28 AM
    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
Page 1
    • Le_Kirk
    • By Le_Kirk 7th Jul 17, 9:50 AM
    • 1,880 Posts
    • 941 Thanks
    Le_Kirk
    • #2
    • 7th Jul 17, 9:50 AM
    • #2
    • 7th Jul 17, 9:50 AM
    Apart from writing a Macro (visual basic) you could use Find & Replace but it is a very manual action.
    • Andy L
    • By Andy L 7th Jul 17, 10:03 AM
    • 8,370 Posts
    • 6,614 Thanks
    Andy L
    • #3
    • 7th Jul 17, 10:03 AM
    • #3
    • 7th Jul 17, 10:03 AM
    You could use conditional formatting rather than a function
    • DoaM
    • By DoaM 7th Jul 17, 10:11 AM
    • 2,730 Posts
    • 2,756 Thanks
    DoaM
    • #4
    • 7th Jul 17, 10:11 AM
    • #4
    • 7th Jul 17, 10:11 AM
    I've used conditional formatting but it highlighting all duplicates
    Originally posted by Verb
    What's the difference between a duplicate and a matching value?
    Diary of a madman
    Walk the line again today
    Entries of confusion
    Dear diary, I'm here to stay
    • ThemeOne
    • By ThemeOne 7th Jul 17, 10:19 AM
    • 987 Posts
    • 811 Thanks
    ThemeOne
    • #5
    • 7th Jul 17, 10:19 AM
    • #5
    • 7th Jul 17, 10:19 AM
    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.
    • DavidP24
    • By DavidP24 7th Jul 17, 12:18 PM
    • 1,932 Posts
    • 1,142 Thanks
    DavidP24
    • #6
    • 7th Jul 17, 12:18 PM
    • #6
    • 7th Jul 17, 12:18 PM
    I would suggest your put the data you are to search from in one table (tab)

    Then use another tab to list the data you are looking for, use the LOOKUP function

    You do not really say why you are doing this so hard to give you best approach

    For example, I once designed a worksheet to define optimum dates within a year of data, the management only want to see those dates on one tab.

    So I started with a sheet for every day of the year, with a columns for around 50 variables

    I then used nested if then else formulas in each cell of each column to note whether there had been a change in value between today and yesterday.

    A second tab was used to collect all those dates and the values therein

    A user then looked at those and put in an entry to select those they would use

    These were then selected automatically by the first tab.

    Later I automated the the source data collection from a plethora of databases and a few manual imports.

    That was the basis to test the figures before code was written, however, the company liked the simplicity of the spreadsheet so much they stuck with it and use it as a template for 150 plus spreadsheets they create every year.

    They are still using it today, around 25 to 30 years later!
    Thanks, don't you just hate people with sigs !
    • Verb
    • By Verb 7th Jul 17, 1:59 PM
    • 159 Posts
    • 5 Thanks
    Verb
    • #7
    • 7th Jul 17, 1:59 PM
    • #7
    • 7th Jul 17, 1:59 PM
    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?
    Originally posted by ThemeOne

    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
    • By Verb 7th Jul 17, 2:01 PM
    • 159 Posts
    • 5 Thanks
    Verb
    • #8
    • 7th Jul 17, 2:01 PM
    • #8
    • 7th Jul 17, 2:01 PM
    What's the difference between a duplicate and a matching value?
    Originally posted by DoaM

    There could be loads of duplicates and only 1 matching value
    • DoaM
    • By DoaM 7th Jul 17, 2:45 PM
    • 2,730 Posts
    • 2,756 Thanks
    DoaM
    • #9
    • 7th Jul 17, 2:45 PM
    • #9
    • 7th Jul 17, 2:45 PM
    There could be loads of duplicates and only 1 matching value
    Originally posted by Verb
    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).
    Diary of a madman
    Walk the line again today
    Entries of confusion
    Dear diary, I'm here to stay
    • rmg1
    • By rmg1 7th Jul 17, 3:22 PM
    • 2,862 Posts
    • 731 Thanks
    rmg1
    If you don't want A1 to be highlighted, then just exclude it from the conditional formatting.
    Or am I also misreading the question?
    Flagellation, necrophilia and bestiality - Am I flogging a dead horse?

    Any posts are my opinion and only that. Please read at your own risk.
    • Cornucopia
    • By Cornucopia 7th Jul 17, 3:29 PM
    • 8,812 Posts
    • 8,195 Thanks
    Cornucopia
    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).
    Originally posted by DoaM

    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?
    Last edited by Cornucopia; 07-07-2017 at 3:43 PM.
    I'm a Board Guide on the Phones & TV, Techie Stuff, In My Home,
    The Money Savers Arms and Food Shopping boards. I'm a volunteer to help the boards run smoothly, and I can move and merge threads there. Any views are mine and not the official line of moneysavingexpert.com.

    Board guides are not moderators. If you spot an inappropriate or illegal post then please report it to forumteam@moneysavingexpert.com
    • Chino
    • By Chino 7th Jul 17, 3:44 PM
    • 367 Posts
    • 169 Thanks
    Chino
    what makes a matching cell a duplicate and what makes a matching cell a matching value? How are the two things different?
    Originally posted by DoaM
    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.
    • Cornucopia
    • By Cornucopia 7th Jul 17, 3:54 PM
    • 8,812 Posts
    • 8,195 Thanks
    Cornucopia
    It's unclear at the moment what the OP means by "duplicate" and "matching".

    Anyway, with a bit of tinkering, I have the following formula to highlight cell A1 when it matches any of the values in column B. This formula needs to go into the conditional formatting field when using the option "use a formula to decide which cells to format".

    =NOT(ISERROR(MATCH(A1,B:B,0)))
    I'm a Board Guide on the Phones & TV, Techie Stuff, In My Home,
    The Money Savers Arms and Food Shopping boards. I'm a volunteer to help the boards run smoothly, and I can move and merge threads there. Any views are mine and not the official line of moneysavingexpert.com.

    Board guides are not moderators. If you spot an inappropriate or illegal post then please report it to forumteam@moneysavingexpert.com
    • Andy L
    • By Andy L 7th Jul 17, 4:40 PM
    • 8,370 Posts
    • 6,614 Thanks
    Andy L
    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.
    Originally posted by Chino
    You don't use the "Duplicate Values" option, you need to use the "Equal To" option which asks you for either a value or a cell reference

    Edit - this is in Excel 2007 if its important
    Last edited by Andy L; 07-07-2017 at 4:45 PM.
    • Andy L
    • By Andy L 7th Jul 17, 4:44 PM
    • 8,370 Posts
    • 6,614 Thanks
    Andy L
    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).
    Originally posted by DoaM
    You could have 'a' in 3 different cells, 'b' in 4 cells, 'c' in one cell & 'd' in one cell & 'x' in A1 as your matching criteria

    Matching duplicates highlights all the 'a's & 'b's as they are duplicates of something else in the sheet.
    • Hedgehog99
    • By Hedgehog99 7th Jul 17, 4:44 PM
    • 1,246 Posts
    • 2,574 Thanks
    Hedgehog99
    Can you use an IF function?
    • ThemeOne
    • By ThemeOne 7th Jul 17, 5:37 PM
    • 987 Posts
    • 811 Thanks
    ThemeOne
    Let's say the value in cell A1 is 25, and that is the value you want highlighted throughout the worksheet.

    Go to Conditional Formatting / New Rule - select "use a formula ..."

    In the "format values where this formula is true" box type =A1=25.

    Click the Format button and choose whatever highlight colour you'd like, then click OK.

    Now go to Conditional Formatting / Manage Rules - delete what is in the "Applies to" box then click the button on the spreadsheet to the left of column A and above row 1 - this will apply the conditional formatting to the whole worksheet. Click Apply and hopefully your highlights will appear.
Welcome to our new Forum!

Our aim is to save you money quickly and easily. We hope you like it!

Forum Team Contact us

Live Stats

1,088Posts Today

7,098Users online

Martin's Twitter
  • Byebye! I'm about to stop work & twitter, to instead spend glorious time with Mrs & mini MSE. Wishing u a lovely summer. See u in 10 days.

  • WARNING Did you start Uni in or after 2012? The interest's rising to 6.1%; yet it doesnt work like you think. See https://t.co/IQ8f0Vyetu RT

  • RT @JanaBeee: @MartinSLewis Boris is the anomaly (coffee), the others are versions of normal (beer). Lots of same candidates = vote share d?

  • Follow Martin