• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Conditional Formatting for Duplicates

Eoin

New Member
Hi All,

I am new to Chandoo and definitely not an excel expert so my query may be pretty simple :) But I have searched the web and cannot find a solution so any help would be really appreciated. I am trying to apply conditional formatting to a work roster (I have uploaded a simplified version of the roster format). The roster is for various supervisors (Columns B to I) looking after various work areas (Rows 5 to 16) and it also shows planned annual/parental leave (Rows 18 to 21). My end goal is to automatically fill cells listing the supervisors name in the top section with red using conditional formatting when a supervisor is rostered for work on a day they are also listed for annual/parental leave. I am trying to use a Countif rule to identify duplicates but as some supervisors can look after more than one area on a particular day I am struggling! The actual roster is very large with sub work areas etc but if I can figure it out for my simplified version I should be able to apply the solution to the actual roster.

I hope i have explained my issue clearly but if not please feel free to ask a question. Again thank you very much in advance for any help :)

Eoin
 

Attachments

  • Roster Sample.xlsx
    42.5 KB · Views: 8
Eoin

Select the range B5:I16
Conditional Formatting
Use a Formula
=AND(B5<>"",COUNTIF($B5:$I5,B5)>1)
Select an appropriate format
Apply
 
Hi Hui,

Thank you for your quick response and apologies I am only getting to look at this again now. I have tried the formula you have provided but unfortunately it does not do what I was hoping. I don't think I explained what I am trying to achieve clearly, sorry :)

Basically I am trying to search each individual column to check if any name listed in rows 17 to 22 are duplicated above in the same column in rows 5 to 16 and apply the format to any duplication found in rows 5 to 16.
I have uploaded a revised version of the roster. Looking at Column B for example I want to search for a duplicate of any name listed in B17 to B22 (highlighted in green) within B5 to B16 (highlighted in yellow). In this case Mary is down for annual leave (Cell B18) but is scheduled for Work Areas 1 (Cell B5) and 4 (Cell B8) and hence the cells need to be highlighted in red.

I hope this a better explanation and you might be able to provide some further guidance. Thank you again for your reply, much appreciated.

Cheers,
Eoin
 

Attachments

  • Roster Sample Rev 1.xlsx
    13.8 KB · Views: 3
Hi Eoin,

Not sure whether I understood your requirement, but see the attached file.

Regards,
 

Attachments

  • Roster Sample Rev 1.xlsx
    13.9 KB · Views: 2
Select the range B5:I16
Conditional Formatting
Use a Formula
=COUNTIF($B$17:$B$22,$B5)>0
Select an appropriate format
Apply
 
Or a simpler formula , Select range B5:I16, and apply below formula in CF.

=AND(COUNTIF(B$5:B$16,B5)>1,COUNTIF(B$17:B$22,B5)=1)

Regards,
 
Or a simpler formula , Select range B5:I16, and apply below formula in CF.

=AND(COUNTIF(B$5:B$16,B5)>1,COUNTIF(B$17:B$22,B5)=1)

Regards,

Hi Somendra,

Thank you for your response above, I have been side tracked in work the last few weeks and I have only gotten back to this problem now, sorry :)
I have applied your formula above (see screenshot attached "Conditional Formatting Formula") and although it does what I was hoping for in some columns, it has not worked for all duplicates. In the second screenshot attached "After CF Applied" I have highlighted in yellow the duplicate cells that CF should also have been applied to but wasn't e.g. Cell C19 Trevor is duplicated in Cell C9 above but Cell C9 has not turned red? Likewise with cells G18 and G14 where Jerry is duplicated.

Any further help would be much appreciated, thank you :)

Eoin
 

Attachments

  • After CF Applied.png
    After CF Applied.png
    325.3 KB · Views: 1
  • Conditional Formatting Formula.png
    Conditional Formatting Formula.png
    359.2 KB · Views: 2
Hi Eoin,

Nice to see your feedback, I think there is some confusion at my end.

See this file. I had changed the formula.

Regards,
 

Attachments

  • Roster Sample Rev 1.xlsx
    13.9 KB · Views: 5
Back
Top