Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 04-02-2021, 06:42 PM
Peterson Peterson is offline Conditional formatting showing duplicates across multiple columns not NOT same row Windows 10 Conditional formatting showing duplicates across multiple columns not NOT same row Office 2019
Competent Performer
Conditional formatting showing duplicates across multiple columns not NOT same row
 
Join Date: Jan 2017
Posts: 141
Peterson is on a distinguished road
Default 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.


  • For example, below, row Job1 has May 02 twice. I don't want May 02 to show as a duplicate because they're on the same row.
  • Row Job2 has May 15, but so does row Job2, so I DO want these to show as duplicates.
I'm attaching a sample doc and would be grateful for any help. My conditional formatting chops are limited to out-of-the-box stuff, and as noted, wasn't able to get what I found online to work. Thank you!

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
Attached Files
File Type: xlsx NoDupesUnlessSameRow.xlsx (10.1 KB, 7 views)
Reply With Quote
  #2  
Old 04-04-2021, 05:37 AM
p45cal's Avatar
p45cal p45cal is offline Conditional formatting showing duplicates across multiple columns not NOT same row Windows 10 Conditional formatting showing duplicates across multiple columns not NOT same row Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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
However, a longer formula can be used which will also exclude columns K and H and is in place on Sheet1 (2), it's based on the likes of:
Code:
=(COUNTIF($F$2:$G$4,F2)+COUNTIF($I$2:$J$4,F2)+COUNTIF($L$2:$M$4,F2)-COUNTIF($F2:$M2,F2))>0
Even this second one isn't perfect because really I should have done the same (split into 3 areas) the -COUNTIF($F2:$M2,F2) part! viz.:
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
This third one is NOT in the attached.

Both sheets show the formula used in columns O:V (these ranges can be deleted).
Attached Files
File Type: xlsx msofficeForums46739NoDupesUnlessSameRow.xlsx (13.5 KB, 13 views)
Reply With Quote
  #3  
Old 04-05-2021, 09:43 PM
Peterson Peterson is offline Conditional formatting showing duplicates across multiple columns not NOT same row Windows 10 Conditional formatting showing duplicates across multiple columns not NOT same row Office 2019
Competent Performer
Conditional formatting showing duplicates across multiple columns not NOT same row
 
Join Date: Jan 2017
Posts: 141
Peterson is on a distinguished road
Default

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.
Reply With Quote
Reply

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Conditional formatting showing duplicates across multiple columns not NOT same row 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
Conditional formatting showing duplicates across multiple columns not NOT same row Using Conditional Formatting to identify duplicates namedujour Excel 3 05-24-2012 05:55 PM

Other Forums: Access Forums

All times are GMT -7. The time now is 06:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft