Highlight duplicate data if it falls within same dates or prevent duplicate data from being used

ShoYnn

Board Regular
Joined
Mar 20, 2019
Messages
60
Office Version
  1. 2016
Platform
  1. Windows
Sorry for the long title. This one is driving me batty. I have looked through Google for an answer, and the answers I found work if the data is arranged in the way the examples are written for, but for how my data is arranged it does not work.

Sheet explanation: the sheet is a n ever expanding work list where teams can be assigned to the task. Each column is a new task. Row 2 in the column has the start date, row 3 has the end date, and rows 5 through 11 have a drop down list of team members to assign to the task. The first task is going to be in column B, the next task in column C, so on and so forth.

WWhat I am looking to do is either prevent the user from assigning a team member to multiple tasks if the days for any of those tasks overlap, or at least have conditional formatting that highlights every instance of that member if they are assigned in an overlapping timeframe.

TThe examples I have seen work if the name is in one column, the start date in the next, and the end date in the third, and all the data already exists, but as mine is arranged via rows and I want to compare those to the next column, and my list is dynamic in that it will continually grow, I am having a hard time figuring out how to make it work for me. Any help would be greatly appreciated!

HHere is an example of what I am looking for/talking about Highlight records based on overlapping date ranges and a condition
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Here is a visual representation of what my data looks like:

A B C D
1 Job job 1. Job 2
2 start. 10/18/2021. 10/19/2021
3 end. 10/22/2021 10/30/2021
4 team lead. Name name
5 member name name
6 member. Name name
7 member name. Name
8 member. Name name
9 member name. Name
10 member name. Name
11 member name. Name

So as the first post mentions, I am looking for something that compares the names in B5:B11 to the names in C5:C11 and if there is a match and the dates in B2:B3 overlap with the dates in C2:C3 will either, through conditional formatting, highlights the matching names or a way to grey out or remove names from the drop down lists, but if the dates do not overlap then nothing will be different than normal operations. Also, this solution needs to be dynamic as more jobs will be added to the following columns over the remainder of the fiscal year. I hope this helps, and I look forward to seeing some of the cool solutions this community can come up with!
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,924
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top