+ Reply to Thread
Results 1 to 6 of 6

conditional format duplicate value to include adjacent cell in another column

  1. #1
    Registered User
    Join Date
    03-23-2015
    Location
    us
    MS-Off Ver
    365
    Posts
    93

    conditional format duplicate value to include adjacent cell in another column

    I have a column of dates in order in col A. Sometimes a date is repeated in an adjacent row. I would like to highlight these cells which are repeated and the corresponding next cell to right, B1, B2, B3, etc.
    I know how to get the repeated cells in Col A highlighted using "Duplicate Values" in Conditional format, but how do I include the corresponding cells in col B?

    EX:
    A1 1/1/17
    A2 1/2/17
    A3 1/3/17-highlighted
    A4 1/3/17-highlighted
    A5 1/4/17
    A6 1/5/17

    I want B3 & B4 included in the conditional format so B3 & B4 are also highlighted.

    Thank you.

  2. #2
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: conditional format duplicate value to include adjacent cell in another column

    Select A1:B6
    if CF use: =COUNTIF($A$1:$A$6,$A1)>1
    set color

  3. #3
    Registered User
    Join Date
    03-23-2015
    Location
    us
    MS-Off Ver
    365
    Posts
    93

    Re: conditional format duplicate value to include adjacent cell in another column

    thanks for help

  4. #4
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: conditional format duplicate value to include adjacent cell in another column

    Welcome

    read my dancing footnote

  5. #5
    Registered User
    Join Date
    03-23-2015
    Location
    us
    MS-Off Ver
    365
    Posts
    93

    Re: conditional format duplicate value to include adjacent cell in another column

    sandy666,
    Regarding your formula, =COUNTIF($A$1:$A$6,$A1)>1
    I understand the range, $A$1:$A$6
    but how does $A1)>1 find any duplicate in the range?
    I assume that >1 looks for any entry that is ">1" or duplicated but the $A1 confuses me. It would seem that it would only look for a duplicate of A1, not any in the entire range. Does the fact that the row [1] is not absolute mean that the query continues thru the range of rows in col A?
    I assume that by including col B that it would extend the range of the CF to include col B in any found rows in col A.
    Thank you.
    How do I add "Reputation"?

  6. #6
    Banned User!
    Join Date
    02-05-2015
    Location
    San Escobar
    MS-Off Ver
    any on PC except 365
    Posts
    12,168

    Re: conditional format duplicate value to include adjacent cell in another column

    Not A1>1 but COUNTIF($A$1:$A$6,$A1)>1

    use in B1: =COUNTIF($A$1:$A$6,$A1) and copy down
    you will see 1 (no dups) or any other digit (>1) = dups
    function counting dups itself that is why A1 compare to the range $A$1:$A$6
    for CF you need TRUE or FALSE (1 or 0) that is why =COUNTIF($A$1:$A$6,$A1)>1 and in CF will be TRUE or FALSE
    I assume that by including col B that it would extend the range of the CF to include col B in any found rows in col A.
    No, you can do that changing range in CF - Applies to. CF responds to TRUE or FALSE (1, 0) and works on the selected range, so the form used should generate TRUE or FALSE and not some other results.


    Lower left corner of the post to which you want to add rep
    addrep01.gif
    Last edited by sandy666; 06-08-2017 at 08:03 PM.

+ 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. conditional format based on adjacent cell's text
    By doughigh in forum Excel General
    Replies: 2
    Last Post: 03-15-2017, 08:31 AM
  2. Conditional Formatting Based on Adjacent Cell *Format*
    By MacroPolo in forum Excel General
    Replies: 1
    Last Post: 05-18-2016, 09:59 AM
  3. [SOLVED] Conditional format when adjacent cell values match
    By penn01 in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 06-30-2015, 10:13 AM
  4. [SOLVED] Conditional Formatting. If cell in column A=x, then any entry in column B must include y
    By rnbblsmm in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 03-03-2015, 05:17 PM
  5. [SOLVED] Conditional format if lower than cell adjacent
    By Delanos123 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-22-2015, 10:22 AM
  6. Replies: 17
    Last Post: 08-26-2014, 01:05 PM
  7. Replies: 1
    Last Post: 04-26-2010, 04:53 AM

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