Close

Results 1 to 7 of 7
  1. #1
    DF VIP Member
    Gazzr's Avatar
    Join Date
    Dec 2004
    Location
    Manchesta!
    Posts
    317
    Thanks
    2
    Thanked:        3
    Karma Level
    250

    Default Excel Conditional formatting with Formula

    I'm trying to conditional format a column of data based on whether the value in the cell appears in another column. After a bit of googling I have this working for numbers, but it does not like text.

    My actual data is text and numbers - the sheet is just to get the method correct. In the screenshots I would expect "as1234" in column E to be highlighted as "as1234" is present in column A.

    Capture1.PNG
    Capture2.PNG

    Thanks in advance...

  2. #2
    DF VIP Member flipper321's Avatar
    Join Date
    Feb 2003
    Location
    Essex
    Posts
    2,696
    Thanks
    11
    Thanked:        131
    Karma Level
    473

    Default Re: Excel Conditional formatting with Formula

    The conditional format will show if the result of your formula is 1 or above, in your text case it returns text which is false for the purpose of conditional formatting

  3. #3
    DF Rookie yarmash's Avatar
    Join Date
    Jul 2018
    Location
    Warwickshire
    Posts
    23
    Thanks
    16
    Thanked:        15
    Karma Level
    83

    Default Re: Excel Conditional formatting with Formula

    Not sure if this is exactly what you want but there is a 'duplicate values' rule within the conditional formatting function. Select both ranges you want to compare and then create a new duplicate value rule.

    2018-07-30 21_29_38-Book1 - Excel.png
    Last edited by yarmash; 30th July 2018 at 09:32 PM. Reason: Add image

  4. #4
    DF VIP Member chizh's Avatar
    Join Date
    Oct 2000
    Location
    Manchester
    Posts
    610
    Thanks
    30
    Thanked:        131
    Karma Level
    330

    Default Re: Excel Conditional formatting with Formula

    http://spreadsheetpro.net/comparing-...unique-values/

    Try (apply to col E)
    =COUNTIF($A:$A, $E1)
    in one column

    and apply to col A
    =COUNTIF($E:$E, $A1)
    to highlight duplicates in both columns as they are entered
    Last edited by chizh; 30th July 2018 at 10:45 PM.

  5. #5
    DF VIP Member
    Gazzr's Avatar
    Join Date
    Dec 2004
    Location
    Manchesta!
    Posts
    317
    Thanks
    2
    Thanked:        3
    Karma Level
    250

    Default Re: Excel Conditional formatting with Formula

    Thanks for the quick responses. I'm not sure the highlighting of duplicates would work as I actually need to do a bit more than I first asked. I want to be able to apply the formula 3 times to get 3 colour formats. See new screenshot - same as original formula, but pointed at the 3 separate columns of data. My actual data will be just cells containing letters and numbers.
    Capture.PNG

  6. #6
    DF VIP Member chizh's Avatar
    Join Date
    Oct 2000
    Location
    Manchester
    Posts
    610
    Thanks
    30
    Thanked:        131
    Karma Level
    330

    Default Re: Excel Conditional formatting with Formula

    Not tested for duplicates in each column but appears to do what I think you're after. Blue column rules. If 65 is in both Amber and Blue, the resulting selection will only be blue. Might have to play around with the ordering of the rules

    3 different rules.
    Attached Files Attached Files

    3 Thanks given to chizh

    4me2 (13th August 2018),  akimba (9th August 2018),  Gazzr (8th August 2018)  


  7. #7
    DF VIP Member
    Gazzr's Avatar
    Join Date
    Dec 2004
    Location
    Manchesta!
    Posts
    317
    Thanks
    2
    Thanked:        3
    Karma Level
    250

    Default Re: Excel Conditional formatting with Formula

    Thanks - that does exactly what I wanted. Apologies for the delay in replying, I've been away.

Similar Threads

  1. excel for fee
    By bobbobb in forum Programming
    Replies: 8
    Last Post: 7th March 2005, 11:22 PM
  2. Excel graph problem
    By marktb in forum PC Problems
    Replies: 2
    Last Post: 9th October 2002, 04:42 PM
  3. Formula 1 Is Boring
    By Digi Program in forum General Sports
    Replies: 10
    Last Post: 1st October 2002, 04:16 PM
  4. Help building a Formula 1 car body (sort of)
    By bozza in forum Home Audio/Video, Electronic Toys & Gadgets
    Replies: 2
    Last Post: 16th September 2002, 04:05 PM
  5. Formula farce
    By 4me2 in forum General Sports
    Replies: 17
    Last Post: 31st August 2002, 03:31 PM

Social Networking 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
  •