|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Conditional formatting showing duplicates across multiple columns not NOT same row
I'm able to set conditional formatting to show duplicates across multiple, non-contiguous columns. What I'd like to do is, if any duplicates are on the same ROW, then DO NOT conditionally format them as duplicates. I've not been able to get this to work based on info I've found on a couple websites.
Code:
Job Date 1 Date 2 ColumnX Date 4 Date 5 ColumnY Date 8 Date 9 Job1 May 01 May 02 Apr 20 May 02 May 15 Jun 01 Job2 May 15 May 16 May 13 May 17 Jun 01 Jun 15 Job3 Jun 15 Jun 18 Jun 10 Jun 15 May 16 Jun 18 |
#2
|
||||
|
||||
In the attached, on Sheet1, there is conditional formatting to test, however, if you type in 15th May in cell K2 it will highlight some cells despite your not wanting to include column K. If you're not going to have dates in those columns that might suffice for you.
The formatting is based on the likes of: Code:
=(COUNTIF($F$2:$M$4,F2)-COUNTIF($F2:$M2,F2))>0 Code:
=(COUNTIF($F$2:$G$4,F2)+COUNTIF($I$2:$J$4,F2)+COUNTIF($L$2:$M$4,F2)-COUNTIF($F2:$M2,F2))>0 Code:
=(COUNTIF($F$2:$G$4,F2)+COUNTIF($I$2:$J$4,F2)+COUNTIF($L$2:$M$4,F2)-COUNTIF($F2:$G2,F2)-COUNTIF($I2:$J2,F2)-COUNTIF($L2:$M2,F2))>0 Both sheets show the formula used in columns O:V (these ranges can be deleted). |
#3
|
|||
|
|||
Hi there -- I downloaded the file, and what you've put together is JUST what I needed. (I should have been clearer about the empty columns -- they will never have dates.)
THANK YOU so much for taking the time to help me with this -- I really appreciate it! But the person for whom I'm putting together a scheduling spreadsheet will appreciate it even more: an 80-year-old who still works full-time, brilliant but completely distracted, constantly double-booking himself. This will provide a bit of functionality he asked for that I wasn't able to figure out. |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Conditional Formatting - Duplicates - Apply to specified range | music_al | Excel Programming | 1 | 01-25-2017 05:34 AM |
Conditional Formatting showing Tracking Status | brucesw | Outlook | 0 | 12-01-2015 03:21 AM |
Conditional Formatting Duplicates | Joanne | Excel | 6 | 08-05-2013 02:46 AM |
Conditional Formatting for columns | MattMurdock | Excel | 1 | 08-09-2012 10:03 PM |
Using Conditional Formatting to identify duplicates | namedujour | Excel | 3 | 05-24-2012 05:55 PM |